Vlookup in range of cells with LastcellRownumber

[kelseye2eye]

Hi,

I'm new to vba and I need a to run a vlookup on one worksheet based upon the data in another worksheet. The amount of data is variable in both worksheets.

Issues I am facing:

• I want my Vlookup to populate from B2:Lastcellrownumber but I can't get it to work.
• for the Vlookup, what is the best way to look up the range on the other worksheet when the data range is variable?

Dim WS As WorksheetDim Lastcell As Range
Dim LastcellRownumber As Long


Set WS = Worksheets("People")
With WS
    Set Lastcell = .Cells(.Rows.Count, "A").End(xlUp)
    LastcellRownumber = Lastcell.Row
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'Users'![COLOR=#ff0000]here is where I need to be able to have a variable range based upon the amount of data in the 'Users' worksheet[/COLOR],2,0)"

End With

/CODE]

Thanks for any help!

 

[CodeNinja]

You can get a formula version of last row:

=AGGREGATE(14,6,ROW(D:D)/(D:D<>""),1)

This will give the last row in column D that is not "" or Empty.

 

[ZAX]

Here Is The Line Of Code :

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'Users'!R2C2:R" & Cells(Rows.Count, 2).End(xlUp).Row & "C2,2,0)"

ZAX

 

[njimack]

Welcome to the board.

Here's how you write the formula to a variable range of cells...

Sub Test()
Dim Last_Row As Long


Application.ScreenUpdating = False


With Sheets("People")
    Last_Row = Range("A" & Rows.Count).End(xlUp).Row
    
    Range("B2:B" & Last_Row).Formula = "VLOOKUP..."
End With


Application.ScreenUpdating = False


End Sub

Here's how you create a dynamic range that you can refer to in your formula: Excel Names -- Excel Named Ranges

 

[kelseye2eye]

Thanks everyone I ended up setting the last row of both sets of data as variables and it works perfectly in the vlookup!