loop and sum

[av8tordude]

How can I loop through every 3rd column of the Active Row and sum the values

Range A1 - M1 Step 3

Thank you kindly for your help

[Paul_Hossler]

As a sub and UDF

Option Explicit




Sub one()
    Dim i As Long
    Dim x As Double
    
    For i = 1 To 13 Step 3
        x = x + ActiveCell.EntireRow.Cells(1, i).Value
    Next i
    
    MsgBox x
End Sub




'make sure not in A-M
Function two() As Double
    Dim i As Long
    Dim x As Double
    
    For i = 1 To 13 Step 3
        x = x + Application.Caller.EntireRow.Cells(1, i).Value
    Next i
    
    two = x
End Function

[av8tordude]

Thank you kindly Paul

[av8tordude]

I'm trying to loop through each row to execute the first code. Unfortunately, i'm not understanding why its not happening.
The first code works as I expected. It displays the average of the sum of every third column of the active row.
The second, I'm trying to loop through each row to accomplish this task.

Can someone assist me. Thank you kindly

Sub one()Dim i As Long, ii As Long
Dim Cnt As Integer
Dim x As Double


For ii = 2 To 9 Step 3
    If Cells(ActiveCell.Row, ii) <> "" Then
        Cnt = Cnt + 1
    End If
    x = x + Cells(ActiveCell.Row, ii).Value
Next ii


MsgBox x / Cnt
End Sub

    
Sub one()
Dim lr As Long, i As Long, ii As Long
Dim Cnt As Integer
Dim x As Double
         
lr = Range("A" & Rows.Count).End(xlUp).Row


For i = 1 To lr


    For ii = 2 To 9 Step 3
        If Cells(1, ii) <> "" Then
            Cnt = Cnt + 1
        End If
        x = x + Cells(1, ii).Value
    Next ii


Next


MsgBox x / Cnt
End Sub

[Paul_Hossler]

Try this

Changed variable names so I could keep them straight

I get 11.08333333

Option Explicit


Sub one()
    Dim rowLast As Long, rowLoop As Long, colLoop As Long
    Dim cntNonZero As Long
    Dim sumTotal As Double
         
    With ActiveSheet
        rowLast = .Cells(.Rows.Count, 1).End(xlUp).Row


        For rowLoop = 1 To rowLast
            For colLoop = 2 To 9 Step 3
                If .Cells(rowLoop, colLoop).Value <> "" Then
                    cntNonZero = cntNonZero + 1
                    sumTotal = sumTotal + .Cells(rowLoop, colLoop).Value
                End If
            Next colLoop
        Next rowLoop
    End With


    If cntNonZero <> 0 Then MsgBox sumTotal / cntNonZero
End Sub

[av8tordude]

not exactly what i'm trying to accomplish. If my active row

is 1, the result is 4.3333
is 2, the result is 19.3333
is 3, the result is 16.666
is 4, the result is 4

the msgbox is to show each of these results

[Paul_Hossler]

Rearrange the code a bit

Option Explicit


Sub one()
    Dim rowLast As Long, rowLoop As Long, colLoop As Long
    Dim cntNonZero As Long
    Dim sumTotal As Double
         
    With ActiveSheet
        rowLast = .Cells(.Rows.Count, 1).End(xlUp).Row


        For rowLoop = 1 To rowLast
        
            cntNonZero = 0
            sumTotal = 0#
        
            For colLoop = 2 To 9 Step 3
                If .Cells(rowLoop, colLoop).Value <> "" Then
                    cntNonZero = cntNonZero + 1
                    sumTotal = sumTotal + .Cells(rowLoop, colLoop).Value
                End If
            Next colLoop
        
            If cntNonZero <> 0 Then MsgBox sumTotal / cntNonZero
        
        Next rowLoop
    End With


End Sub

[av8tordude]

Perfect. thank you kindly Paul

[jolivanes]

Is this viable at all?

Sub Maybe()
Dim a As Double, i As Long
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False
    Cells(i, 1).Resize(, 9).Replace 0, "=XXX", xlWhole, , False, , False, False
        a = WorksheetFunction.SumIf(Cells(i, 1).Resize(, 9), "<>#Name?") / 3
    Cells(i, 1).Resize(, 9).SpecialCells(xlCellTypeFormulas).Value = 0
Application.ScreenUpdating = False
MsgBox a
Next i
End Sub