Applying vlookup to a range

[Guest]

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

 

[Ardus Petus]

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

HTH

 

[Guest]

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]

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

HTH

 

[Guest]

Thanks Ardus. It was really helpful.
Regards,
Shals