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.Print "Column item not found" getDataPoint = "NOT FOUND" Exit Function End Function