Hi
If I have understood you right, this should do it:
Application.Run "'NWSS Scoring.xls'!Add_Round"
FirstRound = Range("E3").Value
RoundsFlown = Worksheets("Pilot Registration").Range("W8").Value
'Application.Goto Reference:="R3C84" 'Refer to CF3
MyFormula = "=IF(RC[-12]="""","""",SUM('Rnd" & FirstRound & _
":Rnd" & RoundsFlown & "'!RC[5]))"
Range("CF3").FormulaR1C1 = MyFormula
BTW: I would insert a Worksheet reference in the FirstRound variable, to
prevent errors if a wrong sheet is active. You do not have to use
Application.Goto to activate a cell. It's better to use Range("CF3").Select,
or refer directly to the cell when you are manipulating it.
Hopes it helps
Regards,
Per
"DMOORE" skrev i meddelelsen
...
First understand that I make no claims to proficiency in VB, I just
stumble
around going from recording macros to editing in VB until things work, so
if
my structure and formats are goofy and violate good coding practice, I
apologize in advance. Let's move on.
I want to calculate the sum of values in a particular cell of three
worksheets. The names of the worksheets will be in the form of "Rnd x" . I
have a macro to add the three new worksheets that the user can invoke at
any
time up to a total of 20 worksheets, resulting in "x" having a value
between
2 and 20.
Following the insertion of these three special sheets, the user can then
add
more sheets that would not be included in the calculation.
My goal is for the first sheet of the three to provide the value of the
cell
on that worksheet alone, the second sheet to sum the cell value of the
first
and second sheet, and the third sheet to sum the value for the first
through
third sheets.
I have defined variables to use for the value of "x" but I don't know how
to
format the VB code to accept the variables. Here is my code (don't laugh).
Application.Run "'NWSS Scoring.xls'!Add_Round"
Set FirstRound = Range("E3")
Set RoundsFlown = Worksheets("Pilot Registration").Range("W8")
Application.Goto Reference:="R3C84"
ActiveCell.FormulaR1C1 = _
"=IF(RC[-12]="""","""",SUM('Rnd [FirstRound]:Rnd
[RoundsFlown]'!RC[5]))"
So really the question is what is the proper format for VB to recognize a
variable used in a formula inserted into a cell?