# 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

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

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

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
25.9 KB · Views: 1

#### offthelip

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

Replies
3
Views
125
Replies
3
Views
148
Replies
6
Views
83
Replies
8
Views
322
Replies
0
Views
51

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.

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