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
– Shai Rado, Commented Jul 31, 2017 at 7: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
– CCM, Commented Jul 31, 2017 at 8:01
so can you let us know whuch Cell is the ActiveCell at the begining ? — Shai Rado
– Shai Rado, Commented Jul 31, 2017 at 8:03
The activecell is the selections or to be more specific RC[-2]-RC[-1] — CCM
– CCM, Commented Jul 31, 2017 at 8: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
– Luuklag, Commented Jul 31, 2017 at 8:19

Michał Turczyn · Accepted Answer · 2017-07-31 11:15:24Z

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, 2017 at 11:15

answered Jul 31, 2017 at 8:25

Michał Turczyn

41.2k18 gold badges58 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

CCM Over a year ago

That is the selection range I was trying to achieve logically, but it is not working.

Michał Turczyn Over a year ago

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.

CCM · Accepted Answer · 2017-07-31 11:07:42Z

0

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

Thanks for your time guys!

answered Jul 31, 2017 at 11:07

CCM

1262 gold badges4 silver badges17 bronze badges

Collectives™ on Stack Overflow

Excel VBA AVERAGE(variable range)

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related