Formula for VBA
 

HELP!!!

I need to show a formula in a cell which would calculate the average
of a list of numbers. Recording a macro yielded the following results:

Range("A25").Select
ActiveCell.FormulaR1C1 = "=average(R[-18]C:R[-3]C)"


Here's the problem: the list of numbers is always either growing or
shrinking, so the formula would not always be in Cell A25, and we
wouldn't always be offsetting by 18 rows, etc. This would be simple to
do if I din't need to show the formula itself, but I have to.

I tried the following:

a = -18
b = -3
Range("A25").Select
ActiveCell.FormulaR1C1 = "=average(R[a]C:R[b]C)"


(This was done for example purposes - I will be using offsets to
achieve selecting a growing or shrinking range...)

I received an error message - I guess you can't use an R1C1 convention
with variables. Is there another way to do this? I need to show the
formula in the cell due to data manipulation purposes; also, this
would be part of a program which would have other calculations and a
requirement has been that formulas be shown.

Any help on this matter will be greatly appreciated!

Thanks,


Haas

Formula for VBA
 

assuming the data column starts in A6, we want the
average function in the second cell below the column,
leaving a blank cell, without knowing the number of items
in the column.

Your code is:
Range("A6").End(xlDown).Offset(2,0).FormulaR1C1 = _
"=average(R6C:R[-2]C)"

the End(xlDown) method locates the last cell in the
column. The offset(2,0) refers to the cell 2 below this.
In the formula, we know row 6 is the first cell, so we
don't need an offset, R6C specifically points to row 6.
The last cell is two rows above - defined by our Offset -
so we can use R[-1]C to point to this cell.

Patrick Molloy
Microsoft Excel MVP


-----Original Message-----
HELP!!!

I need to show a formula in a cell which would calculate
the average
of a list of numbers. Recording a macro yielded the
following results:

Range("A25").Select
ActiveCell.FormulaR1C1 = "=average(R[-18]C:R[-3]C)"


Here's the problem: the list of numbers is always either
growing or
shrinking, so the formula would not always be in Cell
A25, and we
wouldn't always be offsetting by 18 rows, etc. This
would be simple to
do if I din't need to show the formula itself, but I
have to.

I tried the following:

a = -18
b = -3
Range("A25").Select
ActiveCell.FormulaR1C1 = "=average(R[a]C:R[b]C)"


(This was done for example purposes - I will be using
offsets to
achieve selecting a growing or shrinking range...)

I received an error message - I guess you can't use an
R1C1 convention
with variables. Is there another way to do this? I need
to show the
formula in the cell due to data manipulation purposes;
also, this
would be part of a program which would have other
calculations and a
requirement has been that formulas be shown.

Any help on this matter will be greatly appreciated!

Thanks,


Haas


.