VBA to Sum last row of column based on data from Column A

libby845

New Member
Joined
May 8, 2017
Messages
15
Hello -
I have a spreadsheet that has VBA to sum the final row.
Range("B" & LastRow + 1).Formula = "=(SUM(B10:B" & LastRow & "))"

This worked perfectly until areas of responsibility subtotals were added to the report.
For example Oncology 20
250011130 - 4 East 15
250013456 - 2 North 5
Women and Children 33
250015434 - Pediatrics 18
250014020 - 3 West 5
250036578 - 3 North 10

The current macro sums everything together with a result of 106, however the result we want is 53. I thought about dividing by 2, but I can't because at times the users hide the detail and only show the Area of responsibility, so the result would be incorrect.
The number of departments below Area of responsibility changes constantly as do the total departments.
I think I need a SUMIF type command based on if it starts with a character sum it, and if it starts with a number ignore. I don't know how to write that into the macro statement.

Can anyone help?
 
Just to note the code starts in row 1.
Would it be that there may be text in col B at that point, rather than a number ??
That will give you a runtime 13 error "Type Mismatch"


Hi Michael - Thanks for still putting up with me. That is the error message. The data starts in row 10. I tried changing A to A10, but that didn't work.
 
Upvote 0

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
It's always best to provide ALL information when asking for VBA code....VBA is very logical and requires exact info....try this

Code:
Sub MM1()
Dim lr As Long, r As Long, x As Double
lr = Cells(Rows.Count, "A").End(xlUp).Row
x = 0
y = 0
Z = 0
For r = [color=red]10[/color] To lr
    If not IsNumeric(Mid(Range("A" & r).Value, 1, 1)) Then
    x = x + Range("B" & r).Value
    y = y + Range("G" & r).Value
    Z = Z + Range("H" & r).Value
    End If
Next r
Range("B" & lr + 1).Value = x
Range("G" & lr + 1).Value = y
Range("H" & lr + 1).Value = Z
End Sub
 
Upvote 0

Forum statistics

Threads
1,224,828
Messages
6,181,217
Members
453,024
Latest member
Wingit77

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
Back
Top