# 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 SUMI 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

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.

#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...

#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

#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.MIKEhttp://www.skeptic.com/

Related Solutions

#4
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.

 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
 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.