View Full Version : [SOLVED:] loop and sum
av8tordude
05-14-2020, 05:30 AM
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
05-14-2020, 06:18 AM
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
05-14-2020, 08:09 AM
Thank you kindly Paul
av8tordude
05-14-2020, 04:55 PM
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
05-14-2020, 05:24 PM
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
05-14-2020, 05:38 PM
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
05-14-2020, 06:35 PM
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
05-14-2020, 07:21 PM
Perfect. thank you kindly Paul
jolivanes
05-14-2020, 07:44 PM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.