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