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
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,749
Office Version
  1. 2010
Platform
  1. Windows
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
VBA Code:
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

Board Regular
Joined
Mar 21, 2011
Messages
132
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
VBA Code:
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

Board Regular
Joined
Mar 21, 2011
Messages
132
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

Board Regular
Joined
Mar 21, 2011
Messages
132

ADVERTISEMENT

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

Board Regular
Joined
Mar 21, 2011
Messages
132

ADVERTISEMENT

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!
 

Attachments

  • AUTOSUM.JPG
    AUTOSUM.JPG
    25.9 KB · Views: 1

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,749
Office Version
  1. 2010
Platform
  1. Windows
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:
VBA Code:
   Range(Cells(3 + i, 17), Cells(3 + i, 17)).Formula = "=Sum(" & "P" & startrow & ":P" & i + 2 & " )"
to:
VBA Code:
   Range(Cells(3 + i, 16), Cells(3 + i, 16)).Formula = "=Sum(" & "P" & startrow & ":P" & i + 2 & " )"
 

cgsierra

Board Regular
Joined
Mar 21, 2011
Messages
132
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:
VBA Code:
   Range(Cells(3 + i, 17), Cells(3 + i, 17)).Formula = "=Sum(" & "P" & startrow & ":P" & i + 2 & " )"
to:
VBA Code:
   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

Well-known Member
Joined
Dec 23, 2017
Messages
1,749
Office Version
  1. 2010
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,128,121
Messages
5,628,805
Members
416,342
Latest member
BlueDevil12

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