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, Jul 31 '17 at 07:59
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, Jul 31 '17 at 08:01
so can you let us know whuch Cell is the `ActiveCell` at the begining ? — Shai Rado, Jul 31 '17 at 08:03
The activecell is the selections or to be more specific RC[-2]-RC[-1] — CCM, Jul 31 '17 at 08:05
RC[-2]-RC[-1] is not a cell, unless you know what the origin is. Can you show a sample of your worksheet? — Luuklag, Jul 31 '17 at 08:19
Please check [how to avoid using select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) in Excel VBA, in case you were wondering why @ShaiRado made that remark... — Rik Sportel, Jul 31 '17 at 09:56

Michał Turczyn · Answer 1 · 2017-07-31T11:15:24.287

2

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)

edited Jul 31 '17 at 11:15

answered Jul 31 '17 at 08:25

Michał Turczyn

32,028
14
47
69

That is the selection range I was trying to achieve logically, but it is not working. – CCM Jul 31 '17 at 08:39
You have to make sure that you have selected correct cell when you run this code. When you select for example cells A1 and try to run this, you can offset -13 columns, because you would end up on -12th column... and that's an error. Try to include in your code some smarter ways to refer cell you want. Using `ActiveCell` and `Selection` often leads to errors. – Michał Turczyn Jul 31 '17 at 08:59

score 0 · Accepted Answer · answered Jul 31 '17 at 11:07

0

Found a workaround with ActiveCell.Offset(0, 1).Value = "=AVERAGE(RC[-13]:RC[-2])".

Thanks for your time guys!

answered Jul 31 '17 at 11:07

CCM

136
2
4
17

Excel VBA AVERAGE(variable range)

2 Answers2