CODEWITHSUNDEEP
excelvbavlookup
Ian
Ian

Reputation: 107

Trouble With VBA VLookup

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.

Upvotes: 0

Views: 264

Answers (2)

user3598756
user3598756

Reputation: 29421

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

Upvotes: 2

OldUgly
OldUgly

Reputation: 2119

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

Upvotes: 1

Related Questions