Search code examples
excelvbavlookup

Vlookup with VBA/Excel Macros

I am trying to do something for work. I used SAP script to get some information from SAP and then I merged 2 columns together to get a unique value in column P on the MRPMon worksheet.

The beginning of this shows me copying and pasting the concatenated column to column A on the MRPCont worksheet

Sub Vlookup ( )

Dim sourceColumn As Range
Dim destinationColumn As Range
Dim WSF As Worksheet
Dim WSO As Worksheet
Dim lookupRange As Range
Dim FinalRow1 As Integer
Dim FinalRow2 As Integer
Dim i As Long

set WSF = Worksheets ("MRPMon")
Set WSO = Worksheets ("MRPCont")

'Set the source column range
Set sourceColumn = WSF.Range("P:P")
'Set the destination column range
Set destinationColumn = WSO.Range ("A:A")

'Copy the values from the source column to the destination column

sourceColumn.Copy Destination: =destinationColumn

FinalRow1 = WSF.Cells(Rows.Count, 1).End(xlUp).Row
FinalRow2 = WSO.Cells(Rows.Count, 1).End(×lUp).Row

Set lookupRange = WSF.Range("A1:P" & FinalRow1)

For i = 2 To FinalRow2
WSO.Cells(i,2).Value = Application.WorksheetFunction.VLookup(WSO.Cells(i, 1).Value, lookupRange, 2, False)

Next i

End Sub

This gives me a debug error (Unable to get the vlookup property of the worksheetFunction class) on "WSO.Cells(i,2).Value = Application.WorksheetFunction.VLookup(WSO.Cells(i, 1).Value, lookupRange, 2, False)". Prior to that I was getting N/A

Solution

  • If you get a run-time error with the Vlookup(), then it's best to drop the WorksheetFunction part and instead test the return value (which is an error if there' no match)

    Dim res
'...
'...
For i = 2 To FinalRow2
    res = Application.VLookup(WSO.Cells(i, 1).Value, lookupRange, 2, False)
    If IsError(res) Then rs = "No Match!" 'for example 
    WSO.Cells(i,2).Value = res
Next i