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

HTH
--
AP

"Shal" <Shal@discussions.microsoft.com> a écrit dans le message de
news:34038E2E-9520-45A4-BEFE-4A3131EC4EE3@microsoft.com...
> 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" <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
> > >
> > >
> > >
> >
> >
> >