Using .formular1c1 with the Range/Cells Method

[Grumpy Aero Guy]

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.

 

[Myrna Larson]

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.

 

[Myrna Larson]

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

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

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.

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.

 

[Grumpy Aero Guy]

thankx for the insight !