Error msg with vlookup

[Kelvin]

Does anyone know why I get an error #NAME? from this :

activecell.formulaR1C1 = "= VLOOKUP(RC[-1],Sheet1!C[-1]:C[75],hdr,0)"

hdr has been DIM as an integer. When running the value of hdr=33
If I put 33 in place of hdr qne run the macro, the lookup runs fine?

Any help is appreciated

 

[Bernard Liengme]

The variable "hdr" is named within VBA but you are posting a formula to a
cell. The formula in some cell reads
= VLOOKUP(RC[-1],Sheet1!C[-1]:C[75],hdr,0)
but "hdr" is not a named cell or definition within Excel only within VBA
environment.
best wishes

 

[Kelvin]

Is there a way to use a variable in Vlookup?

--
KWB

The variable "hdr" is named within VBA but you are posting a formula to a
cell. The formula in some cell reads
= VLOOKUP(RC[-1],Sheet1!C[-1]:C[75],hdr,0)
but "hdr" is not a named cell or definition within Excel only within VBA
environment.
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

Does anyone know why I get an error #NAME? from this :

activecell.formulaR1C1 = "= VLOOKUP(RC[-1],Sheet1!C[-1]:C[75],hdr,0)"

hdr has been DIM as an integer. When running the value of hdr=33
If I put 33 in place of hdr qne run the macro, the lookup runs fine?

Any help is appreciated

 

[Rick Rothstein \(MVP - VB\)]

Yes, take it out of the string and concatenate the variable in its place
(that will put in the value in the variable rather than its name).

activecell.formulaR1C1 = "= VLOOKUP(RC[-1],Sheet1!C[-1]:C[75]," & hdr &
",0)"

I got a feeling the above line will word wrap in your newsreader. If it
does, the above was meant to be all on one line.

Rick

Is there a way to use a variable in Vlookup?

--
KWB

The variable "hdr" is named within VBA but you are posting a formula to a
cell. The formula in some cell reads
= VLOOKUP(RC[-1],Sheet1!C[-1]:C[75],hdr,0)
but "hdr" is not a named cell or definition within Excel only within VBA
environment.
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

Does anyone know why I get an error #NAME? from this :

activecell.formulaR1C1 = "= VLOOKUP(RC[-1],Sheet1!C[-1]:C[75],hdr,0)"

hdr has been DIM as an integer. When running the value of hdr=33
If I put 33 in place of hdr qne run the macro, the lookup runs fine?

Any help is appreciated

 

[Kelvin]

Amazing. Works like a charm.
Thanks Rick , that was incredibly helpful.
--
KWB

Yes, take it out of the string and concatenate the variable in its place
(that will put in the value in the variable rather than its name).

activecell.formulaR1C1 = "= VLOOKUP(RC[-1],Sheet1!C[-1]:C[75]," & hdr &
",0)"

I got a feeling the above line will word wrap in your newsreader. If it
does, the above was meant to be all on one line.

Rick

Is there a way to use a variable in Vlookup?

--
KWB

The variable "hdr" is named within VBA but you are posting a formula to a
cell. The formula in some cell reads
= VLOOKUP(RC[-1],Sheet1!C[-1]:C[75],hdr,0)
but "hdr" is not a named cell or definition within Excel only within VBA
environment.
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

Does anyone know why I get an error #NAME? from this :

activecell.formulaR1C1 = "= VLOOKUP(RC[-1],Sheet1!C[-1]:C[75],hdr,0)"

hdr has been DIM as an integer. When running the value of hdr=33
If I put 33 in place of hdr qne run the macro, the lookup runs fine?

Any help is appreciated

 

[Rick Rothstein \(MVP - VB\)]

You are welcome. The key thing to remember is anything you put inside quote
marks is text, characters without any code meaning, and nothing else.

Rick

Amazing. Works like a charm.
Thanks Rick , that was incredibly helpful.
--
KWB

Yes, take it out of the string and concatenate the variable in its place
(that will put in the value in the variable rather than its name).

activecell.formulaR1C1 = "= VLOOKUP(RC[-1],Sheet1!C[-1]:C[75]," & hdr &
",0)"

I got a feeling the above line will word wrap in your newsreader. If it
does, the above was meant to be all on one line.

Rick

Is there a way to use a variable in Vlookup?

--
KWB


:

The variable "hdr" is named within VBA but you are posting a formula
to a
cell. The formula in some cell reads
= VLOOKUP(RC[-1],Sheet1!C[-1]:C[75],hdr,0)
but "hdr" is not a named cell or definition within Excel only within
VBA
environment.
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

Does anyone know why I get an error #NAME? from this :

activecell.formulaR1C1 = "=
VLOOKUP(RC[-1],Sheet1!C[-1]:C[75],hdr,0)"

hdr has been DIM as an integer. When running the value of hdr=33
If I put 33 in place of hdr qne run the macro, the lookup runs
fine?

Any help is appreciated