Trouble With VBA VLookup

0
votes

I am having trouble with the below VLookup since I am using a the full column range as my lookup value. 

Private Sub Worksheet_Activate()

Dim WsFk_Row As Long
Dim WsFk_Clm As Long

Table1 = Sheets("Requirements Update Format").Range("A:A")
Table2 = Sheets("Workstreams Link").Range("A:B")
Set WsFk_Row = Sheets("Requirements Insert Format").Range("I1").Row
Set WsFk_Clm = Sheets("Requirements Insert Format").Range("I1").Column


For Each cl In Table1
    Sheets("Requirements Insert Format").Cells(WsFk_Row, WsFk_Clm) = Application.WorksheetFunction.VLookup(cl, Table2, 2, False)
    WsFk_Row = WsFk_Row + 1
Next cl

End Sub

This code appropriately populates all rows, however once completed I receive the following error "Unable to get the VLookup property of the WorksheetFunction class". This led me to believe the following line is where my issue begins

Table1 = Sheets("Requirements Update Format").Range("A:A")

I tried resolving this by modifying the line as below, however this prevented the process to run at all.

Table1 = Sheets("Requirements Update Format").Range(Range("A1"), Range("A1").End(xlDown).Select)

Any suggestion on how I can resolve this would be greatly appreciated.

2
excelvbavlookup
do you need a Set in front of Table1 and Table2? and I don't think you want set for your next two lines. – Forward Ed
I'd say it's because it didn't find the lookup value: did it actually populate ALL cells? – user3598756
@user3598756 I believe you are correct and the reason is the loop goes one beyond the last populated cell in Table1. What I am hoping to achieve is to avoid this error from occurring by limiting Table1 to include only cells with a value in column A. This would have to be dynamic as the number of rows in that column will change frequently. – Ian
See posted answer (sorry I can't test nor format it properly) which also takes Into account Forward Ed's comment – user3598756
@user3598756 got you covered, I edited your answer and added formatting to the code for you so it will read better. Just need to wait for edits to be approved now. – Forward Ed

2 Answers

2
votes

Edited to substitute "VlookUp" with ".Find" method and handle possible not matchings 

Dim WsFk_Row As Long, WsFk_Clm As Long
Dim found as Range
With Sheets("Requirements Update Format")

    Set Table1 = .Range("A1:A" & .Cells(.Rows.Count,1).End (xlUp).Row)

End With

Set Table2 = Sheets("Workstreams Link").Range("A:B")

WsFk_Row = Sheets("Requirements Insert Format").Range("I1").Row

WsFk_Clm = Sheets("Requirements Insert Format").Range("I1").Column


For Each cl In Table1
   Set found = Table2. Columns(1).Find (What:=cl.Value, LookAt:=xlWhole,  LookIn:=xlValues, MatchCase=True)
   If Not found is Nothing then Sht3.Cells(WsFk_Row, WsFk_Clm) =  found.Offset (,1)
   WsFk_Row = WsFk_Row + 1
Next cl

End Sub
1
votes

I think you were on the right track. Try the following ...

Private Sub Worksheet_Activate()

Dim WsFk_Row As Long, WsFk_Clm As Long
Dim Sht1 As Worksheet, Sht2 As Worksheet, Sht3 As Worksheet
Dim Table1 As Range, Table2 As Range
Dim cl As Range
Dim lastRow As Long

Set Sht1 = Worksheets("Requirements Update Format")
Set Sht2 = Worksheets("Workstreams Link")
Set Sht3 = Worksheets("Requirements Insert Format")

lastRow = Sht1.Range("A" & Sht1.Rows.Count).End(xlUp).Row
Set Table1 = Sheets("Requirements Update Format").Range("A1:A" & lastRow)

lastRow = Sht2.Range("A" & Sht2.Rows.Count).End(xlUp).Row
Set Table2 = Sheets("Workstreams Link").Range("A1:B" & lastRow)

WsFk_Row = Sht3.Range("I1").Row
WsFk_Clm = Sht3.Range("I1").Column

For Each cl In Table1
    Sht3.Cells(WsFk_Row, WsFk_Clm) = Application.WorksheetFunction.VLookup(cl, Table2, 2, False)
    WsFk_Row = WsFk_Row + 1
Next cl

Set Sht1 = Nothing
Set Sht2 = Nothing
Set Sht3 = Nothing
Set Table1 = Nothing
Set Table2 = Nothing

End Sub

It does this:

  • Uses a couple of more objects, hopefully to improve readability
  • Corrects some syntax around Set
  • Calculates the actual size of the tables. This prevents the cl loop from shooting past the end of the usable range.
  • Cleans up the objects at the end - just good practice