how to select dynamic row in vlookup

[Ron_N]

Hi,

i am using the following formula to get the value of 'vt'.
-------------------------------------------------------
'i have used autofilter. So my range keeps changing.

Set rCell = Range("A1").CurrentRegion
Set rCell = rCell.Offset(1, 0).Resize(rCell.Rows.Count - 1, rCell.Columns.Count)

rCell(1,1).Select ' to select the first cell after applying autofilter

vt = Application.WorksheetFunction.Vlookup(Cells(2, 4).Value, VarRateTable, 2, 0)
-------------------------------------------------------
But i want the row number to be dynamic, the row number should take the address from rcell(1,1) and the column to be the 4th column(fixed). Please tell me how to do it.

Thanks
Ron

 

[jmthompson]

Untested, but try

Set rCell = Range("A1").CurrentRegion
Set rCell = rCell.Offset(1, 0).Resize(rCell.Rows.Count - 1, rCell.Columns.Count)

rCell(1,1).Select ' to select the first cell after applying autofilter

vt = Application.WorksheetFunction.Vlookup(Cells(rCell.Row, 4).Value, VarRateTable, 2, 0)

 

[Ron_N]

Thank you Thompson. That was real quick. I will try it straightaway.

Ron

 

[Ron_N]

Hi Thompson,

i tried the code suggested by you. It runs fine the first time. But after the iteration of autofilter, suppose the first visible cell is A5. Even then this code is still picking up A2 as the row while applying vlookup.I want rcell as the chosen row in the lookup formula.
Please tell me where i am going wrong.

Thanks,
Ron