Vba, lookup, error alternative

[Certified]

I have a VBA code the performs a Vlookup. The code works well, but I want to make a change.

Currently, when a match is not found, the code goes to an error handler. However, I want to perform another check before I go to the error handler. I want the code to re-run the Vlookup, but on the second run it will look up a specific string, which I will provide.

For example,

If LOOKUPfound = Application.WorksheetFunction.VLookup(LOOKuptext, LOOKuprange, LOOKupcolumn, Not LOOKupmatch) does not return a value rerun the Vlookup, but change the value.

LOOKUPfound = Application.WorksheetFunction.VLookup(alternate lookup text, LOOKuprange, LOOKupcolumn, Not LOOKupmatch)

The reason why I am doing this is because I am matching data from two separate departments and sometimes these departments use the wrong names for the investments. However, I know what they mean and I can easily match them.

I hope I am making sense

Here is an example of my code.

Sub schedule_9()

Dim i As Integer
Dim LOOKuppath As String
Dim LOOKupfile As String
Dim LOOKupWS As String
Dim LOOKuptext As String
Dim LOOKuprange As Range
Dim LOOKupcolumn As Integer
Dim LOOKupmatch As Boolean
Dim LOOKUPfound As Long
Dim ErrorValue As Integer
Dim subfile As String
Dim Subsheet As String

LOOKuppath = "Z:\Accounting\Reporting\SI15\15Q1\15Q1 SI\SI 1051\Sch 9\"
LOOKupfile = "a - 1q15 Schedule 9 Workbook .xlsx"
LOOKupWS = "Pivot Schedule 9"
subfile = "2q15 GRE SI Submission file.xlsm"
Subsheet = "SCH 9 Detail"

Workbooks.Open Filename:=LOOKuppath & LOOKupfile, UpdateLinks:=False
Workbooks(LOOKupfile).Sheets(LOOKupWS).Activate

' LOOK UP VALUES
For i = 2 To 19
    LOOKuptext = Workbooks(subfile).Sheets(Subsheet).Cells(i, 1)
    Set LOOKuprange = Sheets(LOOKupWS).Columns("A:G")
    LOOKupcolumn = 2
    LOOKupmatch = True
    'On Error Resume Next
    
    'DO LOOK UP
    LOOKUPfound = Application.WorksheetFunction.VLookup(LOOKuptext, LOOKuprange, LOOKupcolumn, Not LOOKupmatch)
    
    'store error value and turn off error handling
    ErrorValue = Err
    On Error GoTo 0
    
    'check error value to see if match found
    If ErrorValue <> 0 Then
            MsgBox "Can 't find " & LOOKuptext
        Else
            Workbooks(subfile).Sheets(Subsheet).Cells(i, 20) = LOOKUPfound
    End If


Next i


End Sub

 

[AlphaFrog]

• Declare LOOKUPfound as a variant to accept a lookup value or an N/A# error.
• Use Application.VLookUp and Not Application.Worksheetfunction.VLookup. If no match is found, this will return an N/A# error without throwing a VBA error.
• Use IsError(LOOKUPfound) to test for a successful match or not

        [COLOR=darkblue]Dim[/COLOR] LOOKUPfound [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
        
        [COLOR=green]'DO LOOK UP[/COLOR]
        LOOKUPfound = Application.VLookup(LOOKuptext, LOOKuprange, LOOKupcolumn, [COLOR=darkblue]Not[/COLOR] LOOKupmatch)
        
        [COLOR=darkblue]If[/COLOR] IsError(LOOKUPfound) [COLOR=darkblue]Then[/COLOR]
            [COLOR=green]'No match found. Lookup alternate[/COLOR]
             LOOKUPfound = Application.VLookup(alternate_lookup_text, LOOKuprange,  LOOKupcolumn, [COLOR=darkblue]Not[/COLOR] LOOKupmatch)
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        
        [COLOR=green]'Test agian for 2nd match found[/COLOR]
        [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] IsError(LOOKUPfound) [COLOR=darkblue]Then[/COLOR]
            [COLOR=green]'Match found[/COLOR]
        [COLOR=darkblue]Else[/COLOR]
            [COLOR=green]'No match found for either[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]

 

[Certified]

That Worked. Thanks so much!!