# A loop to simplify finding the averages of 12 columns?

#### Sa12345

##### New Member
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.``````

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

#### Eric W

##### MrExcel MVP
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
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
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

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
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)))``

#### Sa12345

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

#### Eric W

##### MrExcel MVP
You're welcome! Replies
3
Views
578
Replies
0
Views
371
Replies
7
Views
315
Replies
5
Views
634
Replies
7
Views
570 Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,163,524
Messages
5,832,258
Members
430,121
Latest member
Andy Becker ### 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.

### Which adblocker are you using?    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

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