CODEWITHSUNDEEP
excelvba
Mark Pelletier
Mark Pelletier

Reputation: 1359

Excel VBA - Simple Average Computation

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?

Upvotes: 1

Views: 72

Answers (1)

A.S.H
A.S.H

Reputation: 29352

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.

Upvotes: 3

Related Questions