# Autosum vba

#### cgsierra

##### Board Regular
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
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

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

#### offthelip

##### Well-known Member
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
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
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``````

#### cgsierra

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

• cgsierra Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,164,681
Messages
5,838,750
Members
430,567
Latest member
carid ### 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.

### Which adblocker are you using?    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

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