Using a variable for the 2nd paremeter in a vlookup

[Guest]

Greetings,

I have a variable called myFile$ in my VBA that contains a file name. I
want to use it in the following VLookUp.

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-16],'[&myFile&]TOTAL'!R1C1:R548C11,
10, FALSE)"

When I run my script the variable doesn't resolve to the actual contents of
the variable. Instead I get a dialog box prompting me with "Update Values:"
as the title. When I cancel the ActiveCell indeed confirms that the vlookup
formula contains &myFile& as the second parameter.

The reason I need this is because my range for param 2 to vlookup is in a
different file everytime i run my macro.

Can someone advise as to a better way to do this and/or point me to some
documentation?

Thank you very much!
Jeff

 

[JE McGimpsey]

One way:

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-16],'[" & myFile & _
"]TOTAL'!R1C1:R548C11, 10, FALSE)"

 

[Guest]

You were missing the double quotes
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-16],'[" & myFile & "]TOTAL'!R1C1:R548C11, _
10, FALSE)"

 

[Guest]

My previous posting would of had a errror becausse of the line continuation.
You were missing the doulble quotes.

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-16],'[" &myFile & _
"]TOTAL'!R1C1:R548C11, 10, FALSE)"

 

[Guest]

Thanks Joel and JE! I am getting this to work but Excel 2003 is doing
something funny when i parses this. It doesn't seem to reference the sheet
correctly. The contents of the active cell look something like:

=VLOOKUP('A1','C:\Documents and Settings\Jeff\My
Documents\[testvlookup2.xlsTOTAL]testvlookup2'!$A$1:$A$12, 10, FALSE)

TOTAL is a tab on a worksheet. Any idea on what Excel is thinking when it
parses like this?

Thanks again!
Jeff

My previous posting would of had a errror becausse of the line continuation.
You were missing the doulble quotes.

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-16],'[" &myFile & _
"]TOTAL'!R1C1:R548C11, 10, FALSE)"

Greetings,

I have a variable called myFile$ in my VBA that contains a file name. I
want to use it in the following VLookUp.

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-16],'[&myFile&]TOTAL'!R1C1:R548C11,
10, FALSE)"

When I run my script the variable doesn't resolve to the actual contents of
the variable. Instead I get a dialog box prompting me with "Update Values:"
as the title. When I cancel the ActiveCell indeed confirms that the vlookup
formula contains &myFile& as the second parameter.

The reason I need this is because my range for param 2 to vlookup is in a
different file everytime i run my macro.

Can someone advise as to a better way to do this and/or point me to some
documentation?

Thank you very much!
Jeff

 

[Dave Peterson]

You're mixing R1C1 reference style with A1 reference style.

Either use:
ActiveCell.FormulaR1C1 = ...
or
ActiveCell.Formula = ...

But don't mix them.

Thanks Joel and JE! I am getting this to work but Excel 2003 is doing
something funny when i parses this. It doesn't seem to reference the sheet
correctly. The contents of the active cell look something like:

=VLOOKUP('A1','C:\Documents and Settings\Jeff\My
Documents\[testvlookup2.xlsTOTAL]testvlookup2'!$A$1:$A$12, 10, FALSE)

TOTAL is a tab on a worksheet. Any idea on what Excel is thinking when it
parses like this?

Thanks again!
Jeff

My previous posting would of had a errror becausse of the line continuation.
You were missing the doulble quotes.

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-16],'[" &myFile & _
"]TOTAL'!R1C1:R548C11, 10, FALSE)"

Greetings,

I have a variable called myFile$ in my VBA that contains a file name. I
want to use it in the following VLookUp.

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-16],'[&myFile&]TOTAL'!R1C1:R548C11,
10, FALSE)"

When I run my script the variable doesn't resolve to the actual contents of
the variable. Instead I get a dialog box prompting me with "Update Values:"
as the title. When I cancel the ActiveCell indeed confirms that the vlookup
formula contains &myFile& as the second parameter.

The reason I need this is because my range for param 2 to vlookup is in a
different file everytime i run my macro.

Can someone advise as to a better way to do this and/or point me to some
documentation?

Thank you very much!
Jeff