Hi,
I have to apply vlookup formula to a range which is nto pre-defined.The
script must identify the last row in a column and than apply the formula
after the last used row and 500 cells below that.

What I have done is found then Last row using:
LastRow = objExcel.Cells.Find(What:="*", After:=objExcel.Range("A1"), _
searchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
I use the following formula for Vlookup for an identified range:
Range("$B2:B1000").Formula = _
"=VLOOKUP($A2,'SAP DATA'!$A$12:$B$1000,2,FALSE)"

I replace the above formula as below:
Range("$B"&LastRow &:$1000).Formula = _
"=VLOOKUP($A2,'SAP DATA'!$A$12:$B$1000,2,FALSE)"

This gives syntax error.
It would be rally nice if someone can tell me how can I pass this variable
to the Range.I tried various other options but it doesnot seem to work.


Thanks a lot.

Thanks and Regards,
shal