Autosum vba

[cgsierra]

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]

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:

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

 

[offthelip]

Sorry about that my mistake, try this::

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]

Sorry about that my mistake, try this::

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!

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]

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:

 If inarr(i, 1) = "Total" Then

to

 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

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

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]

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:

 If inarr(i, 1) = "Total" Then

to

 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

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

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:

 If inarr(i, 1) = "Total" Then

to

 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

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

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]

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.