Formula help needed to insert into a range

[chamdan]

Hi,

I am having a problem inserting this formula into the range specified here below;

Code: Select all

Range("$A$5").FormulaR1C1 = "=VLOOKUP(RngVal,Table2,2,FALSE)""

This formula works well and gives me the the result needed in the Immediate Window but when I look at the cell in Range A5 the result is #NAME? why?

You help is appreciated.

Chuck

[HansV]

There shouldn't be an = after VLOOKUP.

Is sheetName a defined name or the name of a variable in your code?

[chamdan]

Hans,

It is a variable in my code.
Chuck

[chamdan]

Corrected as:

Code: Select all

Range("$A$5").FormulaR1C1 = "=VLOOKUP=(RngVal,Table2,2,FALSE)"

I corrected it but it is giving me the following message;

Capture.PNG

Chuck

[HansV]

There should NOT be an = after VLOOKUP, as I mentioned in my first reply.

[chamdan]

Sorry Hans it's my mistake of pasting wrongly here below it is but still giving me the same error message????

Code: Select all

Range("$A$5").FormulaR1C1 = "=VLOOKUP(sheetName,Table2,2,FALSE)"

[Rudi]

If RngVal is a variable, you can try this:

Code: Select all

Range("$A$5").Formula = "=VLOOKUP(" & RngVal & ",Table2,2,FALSE)"

Edit:
Oh...sorry...I see you now have sheetName? (Replace RngVal above with sheetName)
Notice also its .Formula, not .FormulaR1C1

[Rudi]

Another thing.... Since your variable is now called sheetName, I assume you are passing a text string into the formula.
If this is the case, change the line to this...

Code: Select all

Range("$A$5").Formula = "=VLOOKUP(" & """" & sheetName & """" & ",Table2,2,FALSE)"

[chamdan]

Hans

This the result showing in the immediate window:

Code: Select all

Range("$A$5").Formula = "=VLOOKUP(" & sheetName & ",Table2,2,FALSE)"

Code: Select all

?Range("$A$5").Formula
=VLOOKUP(AAC,Table2,2,FALSE)

I want the result to be:
=VLOOKUP("AAC",Table2,2,FALSE)

I tried:

Code: Select all

Range("$A$5").Formula = "=VLOOKUP(" & """ & sheetName & """ & ",Table2,2,FALSE)"

It did not work

Chuck

[Rudi]

It's 4 double quotes on each side.

[chamdan]

Wow! I thought one needed 3 double quote to make a quote. Thank You both!

Resolved.

Chuck