AVERAGE function

[Stretchwickster]

I'm writing some code in which i have a row number stored in an integer variable. I'm trying to do the following and its not working so wondered if anyone has an alternative:

  'rowNum has already been set by previous code and is a valid integer
  ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-2]:R[rowNum]C[-2])"

Clive

 

[PBAPaul]

Stretchwickster

Use the technique

Dim MyFormula as string

MyFormula = "=AVERAGE(RC[-2]:R[" & rowNum & "]C[-2])"
ActiveCell.FormulaR1C1 = MyFormula

Using your version, the worksheet function was trying to use the variable rowNum and so an error occurred.

For functions like this, remember that VBA knows what the value of the variable is, so use VBA to create a function using that variable VALUE.

Hope this helps

Paul