VLOOKUP & Macro

[punkin0601]

I am trying to write a macro with a VLOOKUP formula. I currently have:

Dim cell As Range
Cells.EntireRow.Hidden = False
Range(A15:A & Range("A65536").End(xlUp).Row).Select

For Each cell In Selection

If Left(cell.Text,1) = "1" Then Cells(cell.Row, "b") = "=VLOOKUP(
RC[-1],'VB Input'!R4C5:R824C6,4,0)"

The numbers of rows may vary each time I run the macro. How do I adjust my VLOOKUP formula to accomodate this issue.

Thank you in advance for your help!

 

[tactps]

Why not just have the lookup based on the whole column?

Replace:
'VB Input'!R4C5:R824C6
with
'VB Input'!RC5:RC6

 

[punkin0601]

This is not working for me. It is not considering the whole column when I use it in my macro. Any other suggestions? I have tried searching the board for other posts and cannot find anything.