A loop to simplify finding the averages of 12 columns?

Sa12345

New Member
Joined
Jan 5, 2017
Messages
5
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!

Code:
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.
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,602
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:

Code:
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

New Member
Joined
Jan 5, 2017
Messages
5
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,602
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:

Code:
    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

New Member
Joined
Jan 5, 2017
Messages
5

ADVERTISEMENT

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:

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

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,602
I think you'd want to use:

Code:
    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:

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

Watch MrExcel Video

Forum statistics

Threads
1,122,181
Messages
5,594,720
Members
413,926
Latest member
andycap

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top