Custom lookup using search in VBA

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

Image of excel data

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

1 answer

  • answered 2022-04-23 22:49 ouroboros1

    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
    

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum