To find Vlookup dynamically

[GirishDhruva]

Hi Everyone,
Here i have Vlookup which is auto generated by macro, but i need that with dynamic (means table size may vary EVERY MONTH, so the values with red color may change) can anyone help me with this

ActiveCell.FormulaR1C1 = "=VLOOKUP([COLOR=#b22222]RC[-19][/COLOR],'Live Employees'![COLOR=#b22222]R1C1:R73C10[/COLOR],10,0)"

Thank you in prior

 

[mole999]

see shg answer no.2 > https://www.mrexcel.com/forum/excel-questions/498707-select-last-row-last-col.html

 

[GirishDhruva]

see shg answer no.2 > https://www.mrexcel.com/forum/excel-questions/498707-select-last-row-last-col.html

Hi mole999,
I tried with it but i couldn't solve and can you help me to solve it

Sub vlookup()
Dim LastCol As Integer
Dim LastRow As Long


LastRow = Range("A1" & Rows.Count).End(xlUp).Row
LastCol = Range("A1").End(xlToLeft).Column
ActiveCell.Formula = "=VLOOKUP('A2','Sheet4'!Cells(LastRow,lastcol),2,0)"


End Sub

Thank you in advance

 

[mole999]

Sub vlookup()
Dim LastCol As Integer
Dim LastRow As Long

LastCol = Range("A1").End(xlToLeft).Column
LastRow = Range("A" & Rows.Count).End(xlUp).Row

couple of things, certain words are reserved so avoid calling the sub the same as you have in the code.

in LastRow you defined A1 when you were looking for the number itself, so her I removed 1

 

[GirishDhruva]

Sub vlookup()
Dim LastCol As Integer
Dim LastRow As Long

LastCol = Range("A1").End(xlToLeft).Column
LastRow = Range("A" & Rows.Count).End(xlUp).Row

couple of things, certain words are reserved so avoid calling the sub the same as you have in the code.

in LastRow you defined A1 when you were looking for the number itself, so her I removed 1

Same thing i have tried but same error i am getting back and my code is below and i am trying to do Vlookup from sheet1 to sheet4.

Sub vlookup()
Dim LastCol As Integer
Dim LastRow As Long

Worksheets("Sheet1").Activate
LastCol = Range("A1").End(xlToLeft).Column
LastRow = Range("A" & Rows.Count).End(xlUp).Row
ActiveCell.Formula = "=VLOOKUP('A2','Sheet4'!Cells(LastRow,lastcol),2,0)"


End Sub