Excel VBA AVERAGE(variable range)

Question

I'm trying to use an Excel formula that averages the last 12 month values and compares it to the current month value (in this case RC[-2]-RC[-1] and a 30% variance).

The issue is that the row changes, it is nested in a loop.

Below the code:

Dim i As Long 
Dim o As Double 
Dim p As Double

'some code here

For i = 1 To n
    Selection.Value = "=RC[-2]-RC[-1]"
    o = ActiveCell.Value
    p = Application.Formula.Average(Range("RC[-13]", "RC[-2]"))
    If (o >= (p + p * 3 / 10)) Or (o < (p + p * 3 / 10)) Then
        ActiveCell.Font.Color = vbRed
    End If
    ActiveCell.Offset(1, 0).Select
Next i

Any ideas on how to define that average without a separate average function ?

can you show the rest of the code, users here would prefer to give an answer without using Selection, ActiveCell and Select. — Shai Rado
Thanks for the quick reply! There isn't any rest of the code really. What I am trying to achieve is compare 2 values, one is set as a difference between the previous 2 column values and the other is this last 12 column values for the same row. — CCM
so can you let us know whuch Cell is the ActiveCell at the begining ? — Shai Rado
The activecell is the selections or to be more specific RC[-2]-RC[-1] — CCM
RC[-2]-RC[-1] is not a cell, unless you know what the origin is. Can you show a sample of your worksheet? — Luuklag

Michał Turczyn Michał Turczyn · Accepted Answer · 2017-07-31T08:25:43

Change Selection.Value = "=RC[-2]-RC[-1]" to Selection.Value = Offset(0, -2).Value - Offset(0, -1).Value.

Then, declare some range variable: Dim rng As Range, then set it to range with data from last 12 months and calculate average:

Set rng = Range(Selection.Offset(0, -13), Selection.Offset(0, -2))
p = Application.Average(rng)

Excel VBA AVERAGE(variable range)

2 Answers