Solved Sum every nth cell using excel vba

January 6, 2018 at 01:58:33
Specs: Windows 10
Hi,

I would like to sum every nth cell in each individual sheet and paste in the master sheet.

Sheet 1 - Sum(I26 + I32 + I38 + etc..)
Sheet 2 - Sum(I26 + I32 + I38 + etc..)
Sheet 3 - Sum(I26 + I32 + I38 + etc..)
Master Sheet - Cell (A1) - Sheet1 SUM
Cell(A2) - Sheet2 SUM
Cell(A3) - Sheet3 SUM

I have tried the below code but its not sum every nth value and its giving the first
cell value only. Please help with the coding

Sub everynthvalue()
Dim J As Integer
Dim R As Integer
amount = 0

With Worksheets("Sourced")
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Sourced" Then
            For J = 1 To 5000
                    If IsEmpty(Cells(J, 1)) = True And _
                       IsEmpty(Cells(J + 1, 1)) = True Then
                    
                    For R = 26 To 10000
                    If ws.Cells(R, 9).Row Mod 1 = 0 Then
                    amount = amount + ws.Cells(R, 9).Value
                    Exit For
                    End If
                    
                    Next R

                    .Cells(J, 1) = amount

                 Exit For
               End If
            Next J
        End If
    Next ws
End With

Regards


See More: Sum every nth cell using excel vba

Report •

✔ Best Answer
January 6, 2018 at 14:31:22
Actually, VBA is not required. I'm not saying that it can't be used, just that it is not required. I will look at your VBA when I have a chance, but for now, consider this solution.

With a list of worksheet names in a Column (e.g. B) a formula that includes the INDIRECT function would work.

Using the SUMPRODUCT example at the site that I suggested and modifying it to include INDIRECT function to read the sheet names from Column B, this example would SUM every 8th cell in the referenced range in each sheet as you drag it down Column A:

=SUMPRODUCT((MOD(ROW(INDIRECT(B1&"!$I$26:$I$10000")),8)=0)*(INDIRECT(B1&"!$I$26:$I$10000")))


As for your code, perhaps you could eliminate the MOD function and use Step 8 in your loop to sum every 8th cell.

For R = 26 To 10000 Step 8
 amount = amount + Cells(R, 9)
Next

Lots of choices, I just don't have access to VBA at this time.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code



#1
January 6, 2018 at 07:00:40
I don't have time to look at your code right now, but I'll toss this out.

Would a formula based solution work for you? A few options can be found here...

https://www.extendoffice.com/docume...

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#2
January 6, 2018 at 08:09:05
Thanks for your reply Derby !
I have around 40 to 50 Sheets. so VBA Code is required to sum the cells. Appreciate if you take a look on the code.
Regards

Report •

#3
January 6, 2018 at 11:11:27
As a possible work around that uses a UDF and one Formula, try this:

Go to the link suggested by DerbyDad, there you will find a UDF about half way down the page.
insert the UDF in your workbook.

In the SAME CELL on each worksheet use the UDF to SUM the range in each worksheet.

On your Master sheet, in the cell where you want the Sum of Sums enter the formula:

=SUM(Sheet1:Sheet50!XX1)

If your sheets are named something other than Sheet1, Sheet2, etc
the formula is:

=SUM( FirstSheet : LastSheet ! Cell )

You could also use one of the formulas suggested and get the same result.

See how that works for you.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
January 6, 2018 at 14:31:22
✔ Best Answer
Actually, VBA is not required. I'm not saying that it can't be used, just that it is not required. I will look at your VBA when I have a chance, but for now, consider this solution.

With a list of worksheet names in a Column (e.g. B) a formula that includes the INDIRECT function would work.

Using the SUMPRODUCT example at the site that I suggested and modifying it to include INDIRECT function to read the sheet names from Column B, this example would SUM every 8th cell in the referenced range in each sheet as you drag it down Column A:

=SUMPRODUCT((MOD(ROW(INDIRECT(B1&"!$I$26:$I$10000")),8)=0)*(INDIRECT(B1&"!$I$26:$I$10000")))


As for your code, perhaps you could eliminate the MOD function and use Step 8 in your loop to sum every 8th cell.

For R = 26 To 10000 Step 8
 amount = amount + Cells(R, 9)
Next

Lots of choices, I just don't have access to VBA at this time.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#5
January 6, 2018 at 20:39:25
Thanks Debry, I made small changes like u said, its working fine.

the below is the code.

Sub Macro1()

Dim i As Integer
Dim j As Integer
Dim total As Double


With Worksheets("Sourced")
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Sourced" Then
            For i = 1 To 5000
                    If IsEmpty(Cells(i, 1)) = True And IsEmpty(Cells(i + 1, 1)) = True Then
                        total = 0
                        For j = 5 To 16 Step 5
                            total = total + ws.Cells(j, 1)
                            Next
                        .Cells(i, 1) = total
                    Exit For
               End If
            Next i
        End If
    Next ws
End With

End Sub

message edited by jarlamiv


Report •

#6
January 7, 2018 at 11:44:15
I'm glad you got it working.

Quick question: Is the Sourced workbook the first or last sheet in the workbook?

If so, I think you can make your code a little more efficient by using something like these:

For ws_num = 2 To Sheets.Count

or

For ws_num = 1 To Sheets.Count - 1

Your method requires VBA to check the sheet name 40-50 times and compare it to "Sourced" each time. In this case, it would be a minimal improvement in efficiency, but each improvement adds up as your code gets more and more complicated.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

Ask Question