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

 

[offthelip]

Your code doesn't seem to tie up with your description, however here is some code that will write the correct "sum" equation into column Q wherever there is a "Total" in column P
I have tested in the range P 4 to P15

Sub test()
'inarr = Range("P304:Q585")
' for testing
inarr = Range("P4: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(" & "P" & startrow & ":P" & i + 2 & " )"
   startrow = i + 3
  End If
Next i

End Sub

When you change the start row to 304 you need to change the
Startrow= 304
and also everywhere where I add 3 to i and where I add 2 to i add 300 to these values as well.
Try it on P4 to P15 first

 

[cgsierra]

Your code doesn't seem to tie up with your description, however here is some code that will write the correct "sum" equation into column Q wherever there is a "Total" in column P
I have tested in the range P 4 to P15

Sub test()
'inarr = Range("P304:Q585")
' for testing
inarr = Range("P4: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(" & "P" & startrow & ":P" & i + 2 & " )"
   startrow = i + 3
  End If
Next i

End Sub

When you change the start row to 304 you need to change the
Startrow= 304
and also everywhere where I add 3 to i and where I add 2 to i add 300 to these values as well.
Try it on P4 to P15 first

Thank you for your response. I’m actually looking for a code that will give me the sum in the same column like the example I p

 

[cgsierra]

Hi,
Thank you very much for your response! I’m actually looking for a code that will give me the sum result in the same column (on column P) not in column Q. I only mentioned column Q because I have the same thing going on in column Q, where I have formulas that randomly provide cells equal to “Total”. The autosum would essentially overwrite the formula that is in that cell.

 

[cgsierra]

Hi,
Thank you very much for your response! I’m actually looking for a code that will give me the sum result in the same column (on column P) not in column Q. I only mentioned column Q because I have the same thing going on in column Q, where I have formulas that randomly provide cells equal to “Total”. The autosum would essentially overwrite the formula that is in that cell.

How can I adjust the code to achieve this? Thanks again

 

[cgsierra]

How can I adjust the code to achieve this? Thanks again

 

[cgsierra]

In the image below you can see the highlighted cells where the autosum would need to be inserted. The inserted sum formulas need to overwrite the formula in the cells that equal "Total". IE cell P306 would end up with the formula =sum(P304:P305), cell Q306 would end up with the formula =sum(Q304:Q305), cell P308 with formula =sum(P307), cell Q308 with formula =sum(Q307).
The word Total may appear anywhere in column P and column Q so the vba code would need to place the sum formulas in a dynamic or relative position I suppose. Please let me know if this clarifies. The range of the data to be evaluated by the vba code would be P304:Q585. Thank you!

 

[offthelip]

I am still not quite clear about your requirements but to move the sum from column Q to Column P in the code I wrote you just change the address the formula is written to:
change:

   Range(Cells(3 + i, 17), Cells(3 + i, 17)).Formula = "=Sum(" & "P" & startrow & ":P" & i + 2 & " )"

to:

   Range(Cells(3 + i, 16), Cells(3 + i, 16)).Formula = "=Sum(" & "P" & startrow & ":P" & i + 2 & " )"

 

[cgsierra]

I am still not quite clear about your requirements but to move the sum from column Q to Column P in the code I wrote you just change the address the formula is written to:
change:

   Range(Cells(3 + i, 17), Cells(3 + i, 17)).Formula = "=Sum(" & "P" & startrow & ":P" & i + 2 & " )"

to:

   Range(Cells(3 + i, 16), Cells(3 + i, 16)).Formula = "=Sum(" & "P" & startrow & ":P" & i + 2 & " )"

Ok perfect! So if I have a second set of information in column Q, would I need to set up another macro for it or can this macro handle 2 columns?

 

[offthelip]

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