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
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
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
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
Thank you kindly Paul
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
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
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
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
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
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
Perfect. thank you kindly Paul
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