A formula question

kluitna · Mar 14th, 2002, 01:13 PM

does anyone out here know how to put a variable into a linked formula?

Eaxample:

ActiveCell.FormulaR1C1 = _
"=+'[Estimate Source New Item 1.xls]Estimate (1)'!R9C3"

what I want to be able to do is reference different file names in to link the cells together from workbook to workbook.

Any one have any ideas?

smozgur · Mar 14th, 2002, 01:16 PM

if you will use VBA to change formula then it is just like you wrote:

strfile="[Estimate Source New Item 1.xls]"
ActiveCell.FormulaR1C1 ="='" & strfile & "Estimate (1)'!R9C3"

isn't it?

regards
suat

kluitna · Mar 14th, 2002, 01:30 PM

You are right, thanks allot.. opened my eyes a bit there. New to VBA so I am struggling with the code.

smozgur · Mar 14th, 2002, 01:58 PM

Nice to hear that it is working.

You can visit http://www.thewordexpert.com for VBA and office applications help if you need. Subscribe for newsletter then you can follow our VBA Functions in Excel articles series, first newsletter will be posted in a few days.

Suat

[ This Message was edited by: smozgur on 2002-03-14 13:01 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 14th, 2002, 01:13 PM	#1
kluitna Board Regular Join Date: Mar 2002 Posts: 75	does anyone out here know how to put a variable into a linked formula? Eaxample: ActiveCell.FormulaR1C1 = _ "=+'[Estimate Source New Item 1.xls]Estimate (1)'!R9C3" what I want to be able to do is reference different file names in to link the cells together from workbook to workbook. Any one have any ideas?

Mar 14th, 2002, 01:16 PM	#2
smozgur BatCoder Join Date: Feb 2002 Location: Turkey Posts: 764	if you will use VBA to change formula then it is just like you wrote: strfile="[Estimate Source New Item 1.xls]" ActiveCell.FormulaR1C1 ="='" & strfile & "Estimate (1)'!R9C3" isn't it? regards suat

Mar 14th, 2002, 01:30 PM	#3
kluitna Board Regular Join Date: Mar 2002 Posts: 75	You are right, thanks allot.. opened my eyes a bit there. New to VBA so I am struggling with the code.

Mar 14th, 2002, 01:58 PM	#4
smozgur BatCoder Join Date: Feb 2002 Location: Turkey Posts: 764	Nice to hear that it is working. You can visit http://www.thewordexpert.com for VBA and office applications help if you need. Subscribe for newsletter then you can follow our VBA Functions in Excel articles series, first newsletter will be posted in a few days. Suat [ This Message was edited by: smozgur on 2002-03-14 13:01 ]