Applying vlookup to a range

[Shal]

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
--
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
>
>
>

[Shal]

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
> >
> >
> >
>
>
>

[Ardus Petus]

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
> > >
> > >
> > >
> >
> >
> >

[Shal]

Thanks Ardus. It was really helpful.
Regards,
Shals


"Ardus Petus" wrote:

> 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
> > > >
> > > >
> > > >
> > >
> > >
> > >
>
>

thanks
>