Handling errors with minimal repetition when using Find function

I have a function that essentially searches out a value in a table by looking through the column headers and row headers to locate a data point. The function takes the sheet name, the column header name, and row header name as arguments, and I have an error handler set up if any one of the methods performed with those variables fails. However, each handler is very similar, so there is much repetition. Is there a more clear way to have all errors handled uniquely but not to have to re-write Exit Function and other such things?

```
Function getDataPoint(rowItem As String, searchCol As Integer, colItem As String, searchRow As Integer, shtName As String) As Variant

Dim rowNum As Integer, colNum As Integer
Dim rowNumRng As Range, colNumRng As Range

'Find worksheet to search
On Error GoTo shtNotFound:
With ThisWorkbook.Sheets(shtName)

'Find rowNum where rowItem appears in searchCol
On Error GoTo rowNotFound:
Set rowNumRng = .Columns(searchCol).Find(What:=rowItem, _
After:=.Cells(1, searchCol), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=True)

rowNum = rowNumRng.Row

'Find colNum where colItem appears in searchRow
On Error GoTo colNotFound:
Set colNumRng = .Rows(searchRow).Find(What:=colItem, _
After:=.Cells(searchRow, 1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True)

colNum = colNumRng.Column

getDataPoint = .Cells(rowNum, colNum).Value

End With
On Error GoTo 0
Exit Function

shtNotFound:
Debug.Print "Sheet not found"
getDataPoint = "NOT FOUND"
Exit Function
rowNotFound:
Debug.Print "Row item not found"
getDataPoint = "NOT FOUND"
Exit Function
colNotFound:
Debug.Pr

If you absolutely need to use your Find approach, or if you'd just like to see a centralized error handler, as per you question, then you can use a variable to store the custom error message, and then refer to that variable from the error handler:

Also, in you On Error statement, you don't need the trailing ":" as that indicates that the line will have another statement following the ":"

Function getDataPoint(rowItem As String, searchCol As Integer, colItem As String, searchRow As Integer, shtName As String) As Variant

  Dim rowNum As Integer, colNum As Integer
  Dim rowNumRng As Range, colNumRng As Range
  Dim sError As String

  'Find worksheet to search
  sError = "Sheet not found"
  On Error GoTo itmNotFound
  With ThisWorkbook.Sheets(shtName)
    'Find rowNum where rowItem appears in searchCol
    sError = "Row not found"
    Set rowNumRng = .Columns(searchCol).Find(What:=rowItem, _ After:=.Cells(1, searchCol), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=True)
    rowNum = rowNumRng.Row
    'Find colNum where colItem appears in searchRow
    sError = "Column not found"
    Set colNumRng = .Rows(searchRow).Find(What:=colItem, _ After:=.Cells(searchRow, 1), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=True)
    colNum = colNumRng.Column
    getDataPoint = .Cells(rowNum, colNum).Value
  End With
  On Error GoTo 0
  Exit Function

itmNotFound:
  Debug.Print sError
  getDataPoint = "NOT FOUND"

End Function

StackExchange Code Review Q#123034, answer score: 4