VB-How can I use a variable in a sheet reference?

[DMOORE]

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?

 

[Per Jessen]

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]

Per,

That looks like what I needed, and I appreciate your tips. Unfortunately, I
get a error "Application defined or object defined error" and the line
"Range("CF3") ... "is highlighted.

I have fiddled with it without success. Any idea what is going wrong?

Dave Moore

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

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?

 

[DMOORE]

Per, Never mind, it was a typing error.

Dave

Per,

That looks like what I needed, and I appreciate your tips. Unfortunately, I
get a error "Application defined or object defined error" and the line
"Range("CF3") ... "is highlighted.

I have fiddled with it without success. Any idea what is going wrong?

Dave Moore

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

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?

 

[Per Jessen]

Hi Dave

Thanks for your reply. I'm glad that you found the error.

Always copy code rather than type it in to exclude typo's.

Best regards,
Per

Per, Never mind, it was a typing error.

Dave

Per,

That looks like what I needed, and I appreciate your tips. Unfortunately,
I
get a error "Application defined or object defined error" and the line
"Range("CF3") ... "is highlighted.

I have fiddled with it without success. Any idea what is going wrong?

Dave Moore

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" <[email protected]> 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?