Thanks a lot for the syntax correction.One more thing.
When I apply to use the same syntax for Vlookup it gives a syntax error.

Range("$B" & LastRow + 1 & ":$B1000").Formula = _
"=VLOOKUP("$A" & (LastRow +1) & ,'SAP DATA'!$A$12:$B$1000,2,FALSE)"
It says invalid character for $A

Thanks for the help.

Thanks and Regards,
Shalini Nahata


"Ardus Petus" wrote:

Range("$B"&LastRow &":$B1000").Formula = _

HTH
--
AP

"Shal" a écrit dans le message de
...
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