is it possible to use UsedRange with VLOOKUP?

[korhan]

Here is a piece of my code:



Basically, I am trying to get UsedRange from one workbook and use it as a range in VLOOKUP statement.

'usedRangeDestination.Select
For i = 2 To FinalRowMain
strRecipient = Application.VLookup(wsMain.Cells(i, 3), usedRangeDestination.Address, 3, False)
On Error Resume Next
If Recipient = "Error 2042" Then
'nothing found'
ElseIf Recipient = wsMain.Cells(FinalRowMain, 3) Then
wsMain.Cells(FinalRowMain, 14) = "Match"
End If
Next i
wbDestination.Close
myFileName = Dir


Is this possible?

 

[RickXL]

Hi.

Yes!

Try something like this:

Sub Remote_VBLookup()

    Dim wb As Workbook: Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & "mySpreadsheet.xlsm")
    Dim usedRangeDestination As Range: Set usedRangeDestination = wb.Worksheets("Sheet1").UsedRange

    Dim wsMain As Worksheet: Set wsMain = ThisWorkbook.Worksheets("Sheet1")
    Dim strRecipient As Variant
    
    For i = 2 To 2 'FinalRowMain
        strRecipient = Application.VLookup(wsMain.Cells(i, 3), usedRangeDestination, 3, False)
        On Error Resume Next
        If Recipient = "Error 2042" Then
        'nothing found'
        ElseIf Recipient = wsMain.Cells(FinalRowMain, 3) Then
        wsMain.Cells(FinalRowMain, 14) = "Match"
        End If
    Next i
    
End Sub

 

[korhan]

Thanks.

 

[RickXL]

No problem.