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

HTH
--
AP

"Shal" <Shal@discussions.microsoft.com> a écrit dans le message de
news:023489B8-3CF2-472A-ADC7-9CC747ADA2D3@microsoft.com...
> 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
>
>
>