Using a Variable for filename in ActiveCell.FormulaR1C1 =

[Duchesssix]

I have the following formula that I want to replace va05.XLS with a variable but am not sure of the correct syntax.

ActiveCell.FormulaR1C1 = _
' "=IFERROR(VLOOKUP(RC[-3],va05.XLS!C2:C7,6,FALSE), """")"

 

[Jonmo1]

Try

Dim MyVariable As String
MyVariable = "va05.XLS"
 
ActiveCell.FormulaR1C1 = _
' "=IFERROR(VLOOKUP(RC[-3]," & MyVariable & "!C2:C7,6,FALSE), """")"

 

[Duchesssix]

Thought it worked but it doesn't. i get a Run-time error '1004': Application-defined or object-defined error

 

[Peter_SSs]

I know this is quite old but in responding to your recent 'About This Board' thread, I came across this thread and do have some input.

As a matter of interest, is the va05.XLS in your formula another worksheet name in the active workbook, or another workbook where you might be looking in the first, or only, worksheet?

Apart from the apostrophe on the second line of your code (and jonmo1's), which I presume you have removed, try this which should cope better if the variable contains any spaces.

Dim MyVariable As String

MyVariable = "abc def.xls"
 
ActiveCell.FormulaR1C1 = _
    "=IFERROR(VLOOKUP(RC[-3],'" & MyVariable & "'!C2:C7,6,FALSE), """")"