Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
in a macro how to make a part of the formula a variable
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
in a macro how to make a part of the formula a variable
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" -- Rick (MVP - Excel) "Celia" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
in a macro how to make a part of the formula a variable
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 "Celia" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Making part of a formula a variable | Excel Worksheet Functions | |||
Help make a macro variable | Excel Discussion (Misc queries) | |||
Formula expected end of statement error, typing formula into cell as part of VBA macro | Excel Programming | |||
how do i make one part of my formula bold and the other normal? | Excel Worksheet Functions | |||
how to make macro not dependent on datafile which has date as its part. | Excel Programming |