in a macro how to make a part of the formula a variable

[Celia]

I am trying to copy a cell from one file into another file in a macro. There
are hundreds of files all linking to the same cell so I thought I would set
the formula up so that is has a variable for the filename but I can't figure
out the syntax. The formula should link to cell $r$16 in worksheet
"monthly" in the changing file name and the variable I used for the
changing file name is budget_file. The following didn't work. Can someone
tell me how to do this?


ActiveCell.FormulaR1C1 = "=" & budget_file & 'monthly'!$r$16"

 

[Rick Rothstein]

If I understand what you want correctly, you are missing a quote mark in
front of the first apostrophe...

ActiveCell.FormulaR1C1 = "=" & budget_file & "'monthly'!$r$16"

 

[michdenis]

Hi,

and this,

'-----------------------------------
Dim Path As String, File As String, Sh As String

Path = "c:\"
File = "Test.xlam"
Sh = "Sheet1"
ref = "A2"

ActiveCell.Formula = "='" & Path & "[" & File & "]" & Sh & "'!" & _
Range(ref).Address(, , A1)
'-----------------------------------



"Celia" <[email protected]> a écrit dans le message de groupe de discussion
: (e-mail address removed)...
I am trying to copy a cell from one file into another file in a macro. There
are hundreds of files all linking to the same cell so I thought I would set
the formula up so that is has a variable for the filename but I can't figure
out the syntax. The formula should link to cell $r$16 in worksheet
"monthly" in the changing file name and the variable I used for the
changing file name is budget_file. The following didn't work. Can someone
tell me how to do this?


ActiveCell.FormulaR1C1 = "=" & budget_file & 'monthly'!$r$16"

 

[Roger Govier]

Hi Celia

assuming your variable budget_file has been set up as something like
"'[Accounts2009.xls]"
(Note the single quote before the opening bracket '[ )
then the line should read

ActiveCell.FormulaR1C1 = "=" & budget_file & "monthly'!$r$16"
(note the change from your single quote to a double quote)
--
Regards
Roger Govier

I am trying to copy a cell from one file into another file in a macro.
There
are hundreds of files all linking to the same cell so I thought I would
set
the formula up so that is has a variable for the filename but I can't
figure
out the syntax. The formula should link to cell $r$16 in worksheet
"monthly" in the changing file name and the variable I used for the
changing file name is budget_file. The following didn't work. Can
someone
tell me how to do this?


ActiveCell.FormulaR1C1 = "=" & budget_file & 'monthly'!$r$16"

--
Celia

__________ Information from ESET Smart Security, version of virus
signature database 4735 (20100101) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4735 (20100101) __________

The message was checked by ESET Smart Security.

http://www.eset.com