A loop to simplify finding the averages of 12 columns?

[Sa12345]

Hi, I'm new to VBA and have spent today struggling through a relatively simple piece of code.

I have a table with M columns and 200+ rows. They have all been grouped into categories (using an earlier macro) and one blank row has been inserted between each group.

I've come up with this, which works, but is there a simpler way to do it, rather than repeating it 11 times for each column then 7 times for each category??

I'd really appreciate any help!

Dim AnaEndRow As Integer
Dim AnaStartRow As Integer
Dim AnaAverage1 As Double
Dim AnaAverage2 As Double
Dim AnaAverage3 As Double
Dim AnaAverage4 As Double
Dim AnaAverage6 As Double
Dim AnaAverage7 As Double
Dim AnaAverage8 As Double
Dim AnaAverage9 As Double
Dim AnaAverage10 As Double
Dim AnaAverage11 As Double
Dim AnaAverage12 As Double
Dim Cell As Range
Dim Total1 As Double
Dim Total2 As Double
Dim Total3 As Double
Dim Total4 As Double
Dim Total6 As Double
Dim Total7 As Double
Dim Total8 As Double
Dim Total9 As Double
Dim Total10 As Double
Dim Total11 As Double
Dim Total12 As Double
Dim Count As Integer

Sheets("Types").Activate

AnaStartRow = Range("B:B").Find(what:="Annual", after:=Range("B1"), LookIn:=xlValues, Lookat:=xlWhole, searchorder:=xlByColumns).Row
AnaEndRow = Range("B:B").Find(what:="Annual", after:=Range("B1"), LookIn:=xlValues, Lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlPrevious).Row


For Each Cell In Range("C3:C1000")
If Cell.Row >= AnaStartRow And Cell.Row < AnaEndRow Then
    Total1 = Total1 + Cell.Value
    Count = Count + 1
End If
Next Cell
AnaAverage1 = Total1 / Count
Range("C" & AnaEndRow).Value = AnaAverage1

For Each Cell In Range("D3:D1000")
If Cell.Row >= AnaStartRow And Cell.Row < AnaEndRow Then
    Total2 = Total2 + Cell.Value
End If
Next Cell
AnaAverage2 = Total2 / Count
Range("D" & AnaEndRow).Value = AnaAverage2

For Each Cell In Range("E3:E1000")
If Cell.Row >= AnaStartRow And Cell.Row < AnaEndRow Then
    Total3 = Total3 + Cell.Value
End If
Next Cell
AnaAverage3 = Total3 / Count
Range("E" & AnaEndRow).Value = AnaAverage3


etc.

 

[Eric W]

Welcome to the forum.

I see 2 major ways to improve that. First, use arrays/loops to combine common code into a single section. Second, use the built-in tools of Excel to your advantage. Like this:

Dim AnaAverage(12) As Double
Dim i As Long
Dim AnaEndRow As Integer
Dim AnaStartRow As Integer

    Sheets("Types").Activate

    AnaStartRow = Range("B:B").Find(what:="Annual", after:=Range("B1"), LookIn:=xlValues, Lookat:=xlWhole, searchorder:=xlByColumns).Row
    AnaEndRow = Range("B:B").Find(what:="Annual", after:=Range("B1"), LookIn:=xlValues, Lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlPrevious).Row

    For i = 1 To 12
        AnaAverage(i) = WorksheetFunction.Average(Range(Cells(AnaStartRow, i + 2), Cells(AnaEndRow, i + 2)))
    Next i

Hope this helps!

 

[Sa12345]

Thank you so much for your reply - I've just put it into Excel but am getting a run-time error 1004 ("Unable to get the average property of the worksheetfunction class"), am I not referring to the correct values/cells?

 

[Eric W]

You could get that message if there are no numeric values in the range, it's the equivalent of dividing by zero. Check to see if the range is what you really want.

In this formation:

Range(StartCell, EndCell)

I use the Cell method to define the start and end cells. The anaStartRow and anaEndRow you know about. I use i + 2 for the column references, the first time through the loop i is 1, so i+2 = 3, which is column C, and so on.

Range(Cells(AnaStartRow, i + 2), Cells(AnaEndRow, i + 2))

Use the debugger to look at those values (hover the mouse over the field names when you get the error) and make sure they match what you want.

If you actually have columns with no values in them, and you want a result of 0 in that case, then you could do something like:

    On Error Resume Next
    For i = 1 To 12
        AnaAverage(i) = 0
        AnaAverage(i) = WorksheetFunction.Average(Range(Cells(AnaStartRow, i + 2), Cells(AnaEndRow, i + 2)))
    Next i
    On Error GoTo 0

Although that really raises another huge topic about error handling.

 

[Sa12345]

Ah yes, I've sorted the range out so no more errors - thank you!
If I want to print these values onto the blank rows below each column grouping, would it look something like this:

Range(i & (AnaEndRow + 1)).Value = AnaAverage(i)

 

[Eric W]

I think you'd want to use:

    Cells(AnaEndRow + 1, i + 2).Value = AnaAverage(i)

and if you don't use the average for anything else, you could dispense with the AnaAverage() array altogether like this:

[COLOR=#222222][FONT=Verdana]    Cells(AnaEndRow + 1, i + 2).Value[/FONT][/COLOR] = WorksheetFunction.Average(Range(Cells(AnaStartRow, i + 2), Cells(AnaEndRow, i + 2)))

 

[Sa12345]

Thank you so much for your help Eric, it all works brilliantly!

 

[Eric W]

You're welcome!