excelvbaexcel-2007

# Count average value of every 6th column in one row via VBA (Excel)

Right now I'm writing macro on VBA , I almost finished it, but right now i'm stuck on one issue. I can't understand how should I count average value of range that consists of every 6th column of my table. In excel this formula looks like "=AVG(I3, O3, U3...HE3)"

I tried to use macro recorder but it was just hard-coding average value by repeating the whole formula and every cell address. But my table updates everyday and everyday I add 6 new columns at the end of the table, left from last column and last column is column that stores average value of every 6th column of a row. I tried to do it through loop and I think that I'm pretty close to solving this issue, but I don't understand how to add looped values to a particular cell with formula.

Here is my code:

``````Dim i As Integer
i = 8
For i = 8 To rng.Columns.Count Step 6
rng.Cells(3, rng.Columns.Count) = Application.WorksheetFunction.Average(rng.Cells(3,i))
Next i
``````

rng - is Range variable that stores my table.

With above code I managed to loop through all cells that I need to count avg value, but I don't understand how to get values of these cells to a particular cell that counts avg value. Could you please help me?

Solution

• You can just sum the values then divide by the count of (columns-8)/6.

``````Dim i As Integer
Dim sum as long
i = 8
sum = 0
For i = 8 To rng.Columns.Count Step 6
sum = sum + rng.Cells(3,i).value
Next i

rng.Cells(3, rng.Columns.Count).offset(0,1).value = sum/((rng.columns.count-8)/6) ' I assumed you want the value in rng.columns.count +1, since but change it if you want.
``````

EDIT, can't use rng.columns.count+1 I think since the range ends at the count. Changed it to offset.

Also note that we have no error checking in this code.
I would suggest something like `If Isnumber(rng.Cells(3,i).value) then` before adding to the sum since a string value will break the code