Custom lookup using search in VBA-CodePudding

I am not sure, how to fix this function.

if Isnumber(search) is true, then get value from lookup_table

after 2nd round, it ends

Function Slookup(lookup_value As String, lookup_table As Range) As String

Dim i As Integer
On Error GoTo Err
Dim valuetxt As Boolean
Dim cc As Integer

cc = lookup_table.Rows.Count

For i = 1 To cc
    valuetxt = IsError(Application.WorksheetFunction.Search(lookup_table.Cells(i, 1), lookup_value))
    If Not valuetxt Then
        Slookup = lookup_table.Cells(i, 2)
        Exit Function
    End If
    


Slookup = ExcelError.ExcelErrorNA

Err:

Next i

End Function

CodePudding user response：

Tim Williams is correct in noting that IsError() cannot trap a run-time error (1004). So, your IsError() isn't doing much, except providing the necessary False for If Not valuetxt Then when there is no error.

The real problem lies with the fact that your error handler stays within the loop. On Error GoTo Err will not be triggered once you are already inside Err:. The code fails at the third example, because it is the second time you run into an error.

I.e. in your third example, you'll get an error at IsError(Application.WorksheetFunction.Search("A", "E G F")) and are moved to Err:. However, you're not exiting the loop, so now you'll get another error at IsError(Application.WorksheetFunction.Search("B", "E G F")) and the thing breaks down. (So, your second example actually also encountered an error, but it never encountered another.)

You can fix this as follows:

Function SlookupAdj(lookup_value As String, lookup_table As Range) As String

Dim i As Integer
Dim valuetxt As Boolean
Dim cc As Integer

cc = lookup_table.Rows.Count

For i = 1 To cc

    On Error Resume Next
    valuetxt = IsError(Application.WorksheetFunction.Search(lookup_table.Cells(i, 1), lookup_value))
    If Err Then
        Err.Clear
    Else
        If Not valuetxt Then
            SlookupAdj = lookup_table.Cells(i, 2)
            Exit Function
        End If
    End If

SlookupAdj = ExcelError.ExcelErrorNA

Next i

End Function

In this modified code, we run into the same error, but simply skip it, deal with the fact that there is an error (If Err Then), clear it, and then make sure we never reach the evaluation of If Not valuetxt Then.

Again, Tim Williams is correct in noting that the thing you are trying to do, is more easily accomplished using Instr(). Like so:

Function InstrMethod(lookup_value As String, lookup_table As Range) As String

Dim i As Integer
Dim valuetxt As Boolean
Dim cc As Integer

cc = lookup_table.Rows.Count

For i = 1 To cc

    valuetxt = InStr(lookup_value, lookup_table.Cells(i, 1))
    If valuetxt Then
        InstrMethod = lookup_table.Cells(i, 2)
        Exit Function
    End If
    
Next i

End Function