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