Help with vlookup in VBA

[mellowest]

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

[p45cal]

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

[mellowest]

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.