Using .formular1c1 with the Range/Cells Method

Given a worksheet function that accepts a range of values, Average(Range),
for example.....

What is the method to use the .formulaR1C1 method within VBA to assign the
Average formula to a cell, assuming that I want to define the formula from
within VBA using the Range/Cells method(s).

Example:

Macro recorder provides the following code to define a cell using the
Average function:

ActiveCell.FormulaR1C1 = "=AVERAGE(R[-4]C[-2]:R[12]C[-2])"

Can this be done via something like:

Dim Rindex as Integer
Dim Cindex as Integer

Rindex=3
Cindex=7

strArg= "=" & Range(Cells(Rowindex, Colindex),Cells(Rowindex+7,Colindex))
ActiveCell.FormulaR1C1 = "=" & strArg

and have it parse correctly....

i.e. I would like to be able to manipulate the .FormulaR1C1 line via
variables that get worked upon prior to setting the formula in the cell.

Thank you in advance for the assistance.

--


Frank Bachman
(Grumpy Aero Guy)

If you tried to run the code you show here, you would find out that it won't
work.

For the first thing, you didn't specify the key word AVERAGE as part of the
formula (typo?). For the 2nd, the word AVERAGE must be followed by a left
parenthesis, then the ADDRESS of the range you are talking about, in R1C1
format, then a closing parenthesis.

What you wrote gets the 8 VALUEs from the range G3:G10, not the address.

You have to generate the address of the range in the correct format, as a
string, then incorporate that into the formula. I would do it like this

Dim Rindex as Long 'row numbers can be > 32767
Dim Cindex as Integer

Rindex=3
Cindex=7

strArg = Cells(RowIndex, ColIndex).Resize(8,1).Address(,,xlR1C1)
strArg= "=AVERAGE(" & strArg & ")"
ActiveCell.FormulaR1C1 = strArg

That should produced a formula with absolute references, =AVERAGE($G$3:$G$10).
Look up the address property in Help if you want a formula with relative
references.

On Mon, 31 Jan 2005 23:30:03 -0500, "Grumpy Aero Guy" <fbachman@beer_me.com>
wrote:

>Given a worksheet function that accepts a range of values, Average(Range),
>for example.....
>
>What is the method to use the .formulaR1C1 method within VBA to assign the
>Average formula to a cell, assuming that I want to define the formula from
>within VBA using the Range/Cells method(s).
>
>Example:
>
>Macro recorder provides the following code to define a cell using the
>Average function:
>
>ActiveCell.FormulaR1C1 = "=AVERAGE(R[-4]C[-2]:R[12]C[-2])"
>
>Can this be done via something like:
>
>Dim Rindex as Integer
>Dim Cindex as Integer
>
>Rindex=3
>Cindex=7
>
>strArg= "=" & Range(Cells(Rowindex, Colindex),Cells(Rowindex+7,Colindex))
>ActiveCell.FormulaR1C1 = "=" & strArg
>
>and have it parse correctly....
>
>i.e. I would like to be able to manipulate the .FormulaR1C1 line via
>variables that get worked upon prior to setting the formula in the cell.
>
>Thank you in advance for the assistance.

Sorry, I see I changed the variable names in the code I wrote. Should be

strArg = Cells(RIndex, CIndex).Resize(8,1).Address(,,xlR1C1)

On Mon, 31 Jan 2005 23:34:03 -0600, Myrna Larson
<[email protected]> wrote:

>If you tried to run the code you show here, you would find out that it won't
>work.
>
>For the first thing, you didn't specify the key word AVERAGE as part of the
>formula (typo?). For the 2nd, the word AVERAGE must be followed by a left
>parenthesis, then the ADDRESS of the range you are talking about, in R1C1
>format, then a closing parenthesis.
>
>What you wrote gets the 8 VALUEs from the range G3:G10, not the address.
>
>You have to generate the address of the range in the correct format, as a
>string, then incorporate that into the formula. I would do it like this
>
>Dim Rindex as Long 'row numbers can be > 32767
>Dim Cindex as Integer
>
>Rindex=3
>Cindex=7
>
>strArg = Cells(RowIndex, ColIndex).Resize(8,1).Address(,,xlR1C1)
>strArg= "=AVERAGE(" & strArg & ")"
>ActiveCell.FormulaR1C1 = strArg
>
>That should produced a formula with absolute references,
=AVERAGE($G$3:$G$10).
>Look up the address property in Help if you want a formula with relative
>references.
>
>On Mon, 31 Jan 2005 23:30:03 -0500, "Grumpy Aero Guy" <fbachman@beer_me.com>
>wrote:
>
>>Given a worksheet function that accepts a range of values, Average(Range),
>>for example.....
>>
>>What is the method to use the .formulaR1C1 method within VBA to assign the
>>Average formula to a cell, assuming that I want to define the formula from
>>within VBA using the Range/Cells method(s).
>>
>>Example:
>>
>>Macro recorder provides the following code to define a cell using the
>>Average function:
>>
>>ActiveCell.FormulaR1C1 = "=AVERAGE(R[-4]C[-2]:R[12]C[-2])"
>>
>>Can this be done via something like:
>>
>>Dim Rindex as Integer
>>Dim Cindex as Integer
>>
>>Rindex=3
>>Cindex=7
>>
>>strArg= "=" & Range(Cells(Rowindex, Colindex),Cells(Rowindex+7,Colindex))
>>ActiveCell.FormulaR1C1 = "=" & strArg
>>
>>and have it parse correctly....
>>
>>i.e. I would like to be able to manipulate the .FormulaR1C1 line via
>>variables that get worked upon prior to setting the formula in the cell.
>>
>>Thank you in advance for the assistance.

thankx for the insight !

--


Frank Bachman
(Grumpy Aero Guy)


"Grumpy Aero Guy" <fbachman@beer_me.com> wrote in message
news:[email protected]...
> Given a worksheet function that accepts a range of values, Average(Range),
> for example.....
>
> What is the method to use the .formulaR1C1 method within VBA to assign the
> Average formula to a cell, assuming that I want to define the formula from
> within VBA using the Range/Cells method(s).
>
> Example:
>
> Macro recorder provides the following code to define a cell using the
> Average function:
>
> ActiveCell.FormulaR1C1 = "=AVERAGE(R[-4]C[-2]:R[12]C[-2])"
>
> Can this be done via something like:
>
> Dim Rindex as Integer
> Dim Cindex as Integer
>
> Rindex=3
> Cindex=7
>
> strArg= "=" & Range(Cells(Rowindex, Colindex),Cells(Rowindex+7,Colindex))
> ActiveCell.FormulaR1C1 = "=" & strArg
>
> and have it parse correctly....
>
> i.e. I would like to be able to manipulate the .FormulaR1C1 line via
> variables that get worked upon prior to setting the formula in the cell.
>
> Thank you in advance for the assistance.
>
> --
>
>
> Frank Bachman
> (Grumpy Aero Guy)
>
>
>