Excel VBA - Simple Average Computation

Question

Seems so simple, but I am not getting the expected result.

My attempt, simplified for illustration:

Sub myMacro2()

Dim intLastRow As Long
Dim intLastCol As Long
Dim numAve As Double
Dim i As Integer

intLastRow = ActiveSheet.UsedRange.Rows.Count
intLastCol = ActiveSheet.UsedRange.Columns.Count

For i = 1 To intLastRow Step 1
    numAve = Application.WorksheetFunction.Average(Cells(i, 2), Cells(i, intLastCol))
    MsgBox "Row: " & i & " Ave: " & numAve
Next i

End Sub

My data for this example (5 columns, populated from left to right), empty cells treated as NULL.

A   111         
B   111         
C   111 222 333 444
D   111 222     
E   111 222 333 
F   111 222

The msgBox returns "111" as the average value for each row.

I am pretty sure I am missing something fundamental or obvious, but it is eluding me.

Any suggestions, please?

If you're trying to average the range, then it needs to be .Average(.Range(.Cells(I, 2), .Cells(I, intLastCol))). That's assuming your range is correct. Note also the qualified range and cell objects. — Ambie
– Ambie, Commented Jun 19, 2017 at 22:36

A.S.H · Accepted Answer · 2017-06-19 22:34:45Z

3

You are not defining the range correctly.

Application.WorksheetFunction.Average(Cells(i, 2), Cells(i, intLastCol))

should be:

 Application.WorksheetFunction.Average(Range(Cells(i, 2), Cells(i, intLastCol)))
 '                                    ^^^^^^^

Without that, you are just averaging two cells, not the range joining the two cells.

answered Jun 19, 2017 at 22:34

A.S.H

29.4k6 gold badges25 silver badges50 bronze badges

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

2 Comments

Mark Pelletier Over a year ago

A.S.H. - Thanks, I munged that construction in my example. Ugh!

A.S.H Over a year ago

@MarkPelletier glad to help. By the way, in my tests on your data your code gets 277.5 on row 3. Think of changing the way you define your lastRow and lastCol: the UsedRange is unreliable and gives head-aches on the long run.

Collectives™ on Stack Overflow

Excel VBA - Simple Average Computation

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related