Formulas in VBA
 

Try
Dim Aa As Integer
Dim Bb As Integer
'data range is from A14 to A17 and the formula is put in A18
Aa = 14 'start of data
Bb = 17 'end of data
Range("A18").Value = Application.WorksheetFunction.Average(Range("Sheet 1!A"
& Aa & ":A" & Bb))

"Haas" wrote in message
om...
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

Formulas in VBA
 

John,

Thanks for your help here - however, this did not meet my requirement
of being able to display the formula for the average in the selected
cell. I'd like to have the average show up the cell, but also would
like to be able to click on the cell and instead of seeing the value,
be able to see =AVERAGE(a2:a7 or whatever range it is)...

Can this be done?

Thanks again!

"John Gittins" wrote in message ...
Try
Dim Aa As Integer
Dim Bb As Integer
'data range is from A14 to A17 and the formula is put in A18
Aa = 14 'start of data
Bb = 17 'end of data
Range("A18").Value = Application.WorksheetFunction.Average(Range("Sheet 1!A"
& Aa & ":A" & Bb))

"Haas" wrote in message
om...
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