Consulting

Results 1 to 3 of 3

Thread: Help with vlookup in VBA

  1. #1

    Help with vlookup in VBA

    Hello,

    I cannot figure out how to make this work:

    Sub mylookup()
    
    
        Dim thevalue As String
        Dim wsLookup, wsSource As Worksheet
        Dim sourceLastRow, lookupLastRow As Long
        
        Dim i As Long
        
        'On Error Resume Next
        Set wsLookup = ThisWorkbook.Worksheets("Lookup")
        Set wsSource = ThisWorkbook.Worksheets("Source")
        
        For i = 2 To 11
            thevalue = Application.WorksheetFunction.VLookup(wsLookup.Cells(i, 2), wsSource.Range("B:C"), 2, False)
        Debug.Print thevalue
        Next
        
        If Not IsError(thevalue) Then
        
            thevalue = thevalue
        Else
        
        End If
    
    
    End Sub
    Problem: The last matched value in range "B:C" is returned as many times as the loop runs if I include on Error Resume. If I exclude it, only the matched values return and there are no repeats, but, only because I get a run-time error 1004.

    I know this is super simple, but as a neophyte, any help will help.

    Thanks
    Last edited by Bob Phillips; 05-24-2018 at 02:29 PM. Reason: Added code tags

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    You're getting an error 1004 when the vlookup fails to find a match. When you have On Error Resume Next active it just ignores that line, but it doesn't change the value of thevalue.
    One thing you can do is keep the On Error Resume Next active and reset the contents of thevalue (say to an empty string or a message) within the loop just before doing the vlookup:
    For i = 2 To 11
      thevalue = "not found"
      thevalue = Application.WorksheetFunction.VLookup(wsLookup.Cells(i, 2), wsSource.Range("B:C"), 2, False)
      Debug.Print thevalue
    Next
    A different solution could be to change Dim thevalue As String to just Dim thevalue which will allow thevalue to contain other things than just a string (specifically, an error) and change:
    Application.WorkSheetFunction.VLookup
    to just:
    Application.VLookup
    (it behaves differently when an error is thrown; it puts an error into thevalue),
    remove the On Error Resume Next
    and test thevalue for being an error with If IsError(thevalue) Then, leaving you with something along the lines of:
    Sub mylookup()
    Dim thevalue
    Dim wsLookup, wsSource As Worksheet
    Dim sourceLastRow, lookupLastRow As Long
    Dim i As Long
        
    Set wsLookup = ThisWorkbook.Worksheets("Lookup")
    Set wsSource = ThisWorkbook.Worksheets("Source")
    For i = 2 To 11
      thevalue = Application.VLookup(wsLookup.Cells(i, 2), wsSource.Range("B:C"), 2, False)
      If IsError(thevalue) Then
        Debug.Print thevalue;
        Debug.Print ", ]" & wsLookup.Cells(i, 2) & "[ was not found"
      Else
        Debug.Print thevalue
      End If
    Next
    End Sub
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    P45cal. Thank you. This is just what I needed.

    I am not super sure the vlookup function is the most efficient way of doing this, but it's the one I got to somewhat work.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •