0

I am trying to write a vlookup code that uses the lookups tab as the array (A:B) and the revenue tab where the vlookup is in cell Y2. I need it to fill all the way through column Y.

Sub VLOOKUP()

Dim LookupsLastRow As Long
Dim RevenueLastRow As Long
Dim LookupsSheet As Worksheet
Dim RevenueSheet As Worksheet


'What are the names of our worksheets?
Set LookupsSheet = Worksheets("Lookups")
Set RevenueSheet = Worksheets("Revenue")

'Determine last row of source
With LookupsSheet
    LookupsLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
With RevenueSheet
    'Determine last row in col P
    RevenueLastRow = .Cells(.Rows.Count, "X").End(xlUp).Row
    'Apply our formula
    .Range("Y2:Y" & RevenueLastRow).Formula = _
        "=VLOOKUP(V2,"Lookups"!$A$2:$B$" & LookupsLastRow & ",2,0)"
End With



End Sub
urdearboy
  • 14,439
  • 5
  • 28
  • 58
Daniel Perez
  • 1
  • You already know how to [insert a variable into a formula](https://stackoverflow.com/q/42503316/11683), just put the sheet name [back inside the string](https://stackoverflow.com/q/68549699/11683). – GSerg Jul 29 '21 at 21:01
  • How about modifying it slightly? "=VLOOKUP(V2,Lookups!$A$2:$B$" & LookupsLastRow & ",2,0)" – Wils Mils Jul 30 '21 at 00:33
  • I would recommend looking into Power Query for this. – JoshL Jul 30 '21 at 15:12
  • I would highly recommend dropping `VLookUp()` for `Index( OutputSheet!B:B, Match(InputSheet!A1, OutputSheet!A:A, 0))`. In general, it helps keep straight the input/output and prevents any issues with the reference to X column within the total look-up range, which supports longevity. Note that I used the whole column reference in my example here, though it will always be faster to have a fixed range, e.g., B1:B100. – Cyril Jul 30 '21 at 15:44

0 Answers0