Autosum vba

cgsierra

Board Regular
Joined
Mar 21, 2011
Messages
132
Hello, I'm looking to fix the vba code below. Essentially, I'm looking for a vba code to perform the autosum function in each cell in the range that contains the word "Total". The problem with the code below is that the autosum sums everything above it, including the cells with the totals. I am looking for the autosum to only sum the cells above it BUT below the last total calculated. IE
P304 = 2
P305 = Total
P306 = 3
P307 = 7
P308 = 2
P309 = Total
Cell P309 should autosum 3, 7 and 2 only, NOT 2, 2(first Total), 3,7, and 2. The values of the cells are based on formulas which reference a database. Therefore, the word "Total" appears randomly in the range based on the data refresh. Please help!

VBA code:
  1. 'change sum in subtotal to formula
  2. Set sumrange = Range("P304:Q585")
  3. For Each Cell In sumrange
  4. If Cell.value = "sum" Then
  5. Cell.Formula = "=Sum(" & Range(Cell.Offset(-1, 0), Cell.Offset(-1, 0).End(xlUp)).Address & ")"
  6. End If
  7. Next
 

cgsierra

Board Regular
Joined
Mar 21, 2011
Messages
132
It is possible to modify this sub routine to operate over 2 or more columns by introducing another loop round the columns. However if you just doing 2 columns it is probavbly quicker to just copy the subroutine and change the references to the columns like this:
VBA Code:
Sub test()
'inarr = Range("Q304:Q585")
' for testing
inarr = Range("Q4:Q15")
startrow = 4
For i = 1 To UBound(inarr, 1)
  If inarr(i, 1) = "Total" Then
   Range(Cells(3 + i, 17), Cells(3 + i, 17)).Formula = "=Sum(" & "Q" & startrow & ":Q" & i + 2 & " )"
   startrow = i + 3
  End If
Next i

End Sub
Thank you SO much! I am one step away from having the code I need. The only thing that needs to change now is that when I run the current code it sums the correct cells above it but unfortunately it also includes the cell above it that has the sum formula in it too. The first "total" in the column is fine because there are no "total"s above it. However the second 'total' will include the first total in the sum formula that is inserted by the vba code. The range of the inserted sum formula should not include the "total" cell above it. Hopefully I did not confuse you too much
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,732
Office Version
  1. 2010
Platform
  1. Windows
Sorry about that my mistake, try this::
VBA Code:
Sub test()
'inarr = Range("Q304:Q585")
' for testing
inarr = Range("Q4:Q15")
startrow = 3
For i = 1 To UBound(inarr, 1)
  If inarr(i, 1) = "Total" Then
   Range(Cells(3 + i, 17), Cells(3 + i, 17)).Formula = "=Sum(" & "Q" & startrow + 1 & ":Q" & i + 2 & " )"
   startrow = i + 3
  End If
Next i

End Sub
 

cgsierra

Board Regular
Joined
Mar 21, 2011
Messages
132
Sorry about that my mistake, try this::
VBA Code:
Sub test()
'inarr = Range("Q304:Q585")
' for testing
inarr = Range("Q4:Q15")
startrow = 3
For i = 1 To UBound(inarr, 1)
  If inarr(i, 1) = "Total" Then
   Range(Cells(3 + i, 17), Cells(3 + i, 17)).Formula = "=Sum(" & "Q" & startrow + 1 & ":Q" & i + 2 & " )"
   startrow = i + 3
  End If
Next i

End Sub
It worked! My final code is the below. Thank you so so much!
Rich (BB code):
Sub Automsum()

inarr = Range("O304:P585")

startrow = 303
For i = 1 To UBound(inarr, 1)
  If inarr(i, 1) = "Total" Then
   Range(Cells(303 + i, 15), Cells(303 + i, 15)).Formula = "=Sum(" & "O" & startrow + 1 & ":O" & i + 302 & " )"
   startrow = i + 303
  End If
Next i

startrow = 303
For i = 1 To UBound(inarr, 1)
  If inarr(i, 1) = "Total" Then
   Range(Cells(303 + i, 16), Cells(303 + i, 16)).Formula = "=Sum(" & "P" & startrow + 1 & ":P" & i + 302 & " )"
   startrow = i + 303
  End If
Next i

End Sub
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,732
Office Version
  1. 2010
Platform
  1. Windows
Your code looks as though it might have an error in it: the first loop checks for "Total" in the first element ofthe array inarr which mean it is checking for "total" in column O and it writes the equation in
column O

the second loop also check for "Total" in the first element of the array so it is also checking for "total" in column O but it writes the equation in col P. this doesn't make sense to me. I think you should be checking for "total" in column P instead. To do this you need to change this line:
VBA Code:
 If inarr(i, 1) = "Total" Then
to
VBA Code:
 If inarr(i, 2) = "Total" Then
but only in the second loop
If you do want to write the equation in column P whenever there is "Total" in column O , you only need to have one loop, you can write both lines one after the other. See the second example
Code to check O and then P for Total
VBA Code:
Sub Automsum()
inarr = Range("O304:P585")
startrow = 303
For i = 1 To UBound(inarr, 1)
  If inarr(i, 1) = "Total" Then
   Range(Cells(303 + i, 15), Cells(303 + i, 15)).Formula = "=Sum(" & "O" & startrow + 1 & ":O" & i + 302 & " )"
   startrow = i + 303
  End If
Next i

startrow = 303
For i = 1 To UBound(inarr, 1)
  If inarr(i, 2) = "Total" Then
   Range(Cells(303 + i, 16), Cells(303 + i, 16)).Formula = "=Sum(" & "P" & startrow + 1 & ":P" & i + 302 & " )"
   startrow = i + 303
  End If
Next i

End Sub
Code to do what your code does but faster
VBA Code:
Sub Automsum()

inarr = Range("O304:P585")

startrow = 303
For i = 1 To UBound(inarr, 1)
  If inarr(i, 1) = "Total" Then
   Range(Cells(303 + i, 15), Cells(303 + i, 15)).Formula = "=Sum(" & "O" & startrow + 1 & ":O" & i + 302 & " )"
   Range(Cells(303 + i, 16), Cells(303 + i, 16)).Formula = "=Sum(" & "P" & startrow + 1 & ":P" & i + 302 & " )"
   startrow = i + 303
  End If
Next i

End Sub
 
Solution

cgsierra

Board Regular
Joined
Mar 21, 2011
Messages
132
Your code looks as though it might have an error in it: the first loop checks for "Total" in the first element ofthe array inarr which mean it is checking for "total" in column O and it writes the equation in
column O

the second loop also check for "Total" in the first element of the array so it is also checking for "total" in column O but it writes the equation in col P. this doesn't make sense to me. I think you should be checking for "total" in column P instead. To do this you need to change this line:
VBA Code:
 If inarr(i, 1) = "Total" Then
to
VBA Code:
 If inarr(i, 2) = "Total" Then
but only in the second loop
If you do want to write the equation in column P whenever there is "Total" in column O , you only need to have one loop, you can write both lines one after the other. See the second example
Code to check O and then P for Total
VBA Code:
Sub Automsum()
inarr = Range("O304:P585")
startrow = 303
For i = 1 To UBound(inarr, 1)
  If inarr(i, 1) = "Total" Then
   Range(Cells(303 + i, 15), Cells(303 + i, 15)).Formula = "=Sum(" & "O" & startrow + 1 & ":O" & i + 302 & " )"
   startrow = i + 303
  End If
Next i

startrow = 303
For i = 1 To UBound(inarr, 1)
  If inarr(i, 2) = "Total" Then
   Range(Cells(303 + i, 16), Cells(303 + i, 16)).Formula = "=Sum(" & "P" & startrow + 1 & ":P" & i + 302 & " )"
   startrow = i + 303
  End If
Next i

End Sub
Code to do what your code does but faster
VBA Code:
Sub Automsum()

inarr = Range("O304:P585")

startrow = 303
For i = 1 To UBound(inarr, 1)
  If inarr(i, 1) = "Total" Then
   Range(Cells(303 + i, 15), Cells(303 + i, 15)).Formula = "=Sum(" & "O" & startrow + 1 & ":O" & i + 302 & " )"
   Range(Cells(303 + i, 16), Cells(303 + i, 16)).Formula = "=Sum(" & "P" & startrow + 1 & ":P" & i + 302 & " )"
   startrow = i + 303
  End If
Next i

End Sub
[/QUOTE]

Your code looks as though it might have an error in it: the first loop checks for "Total" in the first element ofthe array inarr which mean it is checking for "total" in column O and it writes the equation in
column O

the second loop also check for "Total" in the first element of the array so it is also checking for "total" in column O but it writes the equation in col P. this doesn't make sense to me. I think you should be checking for "total" in column P instead. To do this you need to change this line:
VBA Code:
 If inarr(i, 1) = "Total" Then
to
VBA Code:
 If inarr(i, 2) = "Total" Then
but only in the second loop
If you do want to write the equation in column P whenever there is "Total" in column O , you only need to have one loop, you can write both lines one after the other. See the second example
Code to check O and then P for Total
VBA Code:
Sub Automsum()
inarr = Range("O304:P585")
startrow = 303
For i = 1 To UBound(inarr, 1)
  If inarr(i, 1) = "Total" Then
   Range(Cells(303 + i, 15), Cells(303 + i, 15)).Formula = "=Sum(" & "O" & startrow + 1 & ":O" & i + 302 & " )"
   startrow = i + 303
  End If
Next i

startrow = 303
For i = 1 To UBound(inarr, 1)
  If inarr(i, 2) = "Total" Then
   Range(Cells(303 + i, 16), Cells(303 + i, 16)).Formula = "=Sum(" & "P" & startrow + 1 & ":P" & i + 302 & " )"
   startrow = i + 303
  End If
Next i

End Sub
Code to do what your code does but faster
VBA Code:
Sub Automsum()

inarr = Range("O304:P585")

startrow = 303
For i = 1 To UBound(inarr, 1)
  If inarr(i, 1) = "Total" Then
   Range(Cells(303 + i, 15), Cells(303 + i, 15)).Formula = "=Sum(" & "O" & startrow + 1 & ":O" & i + 302 & " )"
   Range(Cells(303 + i, 16), Cells(303 + i, 16)).Formula = "=Sum(" & "P" & startrow + 1 & ":P" & i + 302 & " )"
   startrow = i + 303
  End If
Next i

End Sub
This is great stuff! Thank you very much! I assigned the macro to a button which I am copying over to different tabs. For some reason, the macro (or the button) stops working on the 20th tab. Would you know why?
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,732
Office Version
  1. 2010
Platform
  1. Windows
That is an entirely different question, so you need to start a new thread if you want that answered. I can't see any reason why it would stop on the 20th tab.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,581
Messages
5,625,612
Members
416,124
Latest member
DeMoNloK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top