Similar to my last post, I want to be able to change a parameter
within a calculation.

I have tried to modify code that I used for a similar event from
before but I am stumped.

The code is as follows: -

Dim extract As String
ActiveCell.FormulaR1C1 = _
extract = InputBox("Please enter Version Control Master file", "File
Name")
ActiveCell.FormulaR1C1 = _
"=+'[& extract &]Static Summary'!R9C3-'[Version Control Master
20070815.xls]Static Summary'R9C3"

Basically I am calling cell C9 from one sheet whose name changes daily
and subtracting the value in C9 on the other sheet which does not
change.

Responses/guidance gratefully received.
Thank you

Mark

One way:

ActiveCell.FormulaR1C1 = _
"='[" & extract & "]Static Summary'!R9C3-'[Version Control Master
20070815.xls]Static Summary'R9C3"

In article <(E-Mail Removed)>,
(E-Mail Removed) wrote:

> ActiveCell.FormulaR1C1 = _
> "=+'[& extract &]Static Summary'!R9C3-'[Version Control Master
> 20070815.xls]Static Summary'R9C3"

Je,
Tried your suggestion , comes up with a debug error.

I also tried this: -

ActiveCell.FormulaR1C1 = _
"='[" & extract & ".xls""]Static Summary'!R9C3-'[Version
Control Master 20070815.xls]Static Summary

but that just debugs too.

Mark


On 18 Sep, 13:18, JE McGimpsey <jemcgimp...@mvps.org> wrote:
> One way:
>
> ActiveCell.FormulaR1C1 = _
> "='[" & extract & "]Static Summary'!R9C3-'[Version Control Master
> 20070815.xls]Static Summary'R9C3"
>
> In article <1190115443.466122.208...@r29g2000hsg.googlegroups.com>,
>
>
>
> etradeg...@hotmail.com wrote:
> > ActiveCell.FormulaR1C1 = _
> > "=+'[& extract &]Static Summary'!R9C3-'[Version Control Master
> > 20070815.xls]Static Summary'R9C3"- Hide quoted text -
>
> - Show quoted text -

Not sure what 'debug' error you're getting - I'd expect an application
error.

Just noticed that you left out the last exclamation point. Try:

ActiveCell.FormulaR1C1 = _
"='[" & extract & "]Static Summary'!R9C3-'[Version Control
Master 20070815.xls]Static Summary'!R9C3"


='[Workbook1.xls]Static Summary'!R9C3-'[Version Control Master
20070815.xls]Static Summary'!R9C3
In article <(E-Mail Removed)>,
(E-Mail Removed) wrote:

> Je,
> Tried your suggestion , comes up with a debug error.
>
> I also tried this: -
>
> ActiveCell.FormulaR1C1 = _
> "='[" & extract & ".xls""]Static Summary'!R9C3-'[Version
> Control Master 20070815.xls]Static Summary
>
> but that just debugs too.
>
> Mark
>
>
> On 18 Sep, 13:18, JE McGimpsey <jemcgimp...@mvps.org> wrote:
> > One way:
> >
> > ActiveCell.FormulaR1C1 = _
> > "='[" & extract & "]Static Summary'!R9C3-'[Version Control Master
> > 20070815.xls]Static Summary'R9C3"
> >
> > In article <1190115443.466122.208...@r29g2000hsg.googlegroups.com>,
> >
> >
> >
> > etradeg...@hotmail.com wrote:
> > > ActiveCell.FormulaR1C1 = _
> > > "=+'[& extract &]Static Summary'!R9C3-'[Version Control Master
> > > 20070815.xls]Static Summary'R9C3"- Hide quoted text -
> >
> > - Show quoted text -