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

[libby845]

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?

 

[Michael M]

Try this

Sub MM1()
Dim lr As Long, r As Long, x As Double
lr = Cells(Rows.Count, "A").End(xlUp).Row
x = 0
For r = 1 To lr
    If IsNumeric(Mid(Range("A" & r).Value, 1, 1)) Then
    x = x + Range("B" & r).Value
    End If
Next r
Range("B" & lr + 1).Value = x
End Sub

 

[libby845]

Michael -

This works like a champ! I'm going to be greedy and ask, if I need to do the same to other columns in the report let's say B, G, and H should I just use different macro or can that be incorporated into your code above?

 

[Michael M]

Maybe like this

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 = 1 To lr
    If 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

 

[libby845]

Michael - I have to ask one more question. I've been playing with the sheet at it appears it is totaling only the amounts with departments (ifnumeric expression). Is there a way to do the opposite? The reason I ask is because some leaders collapse the detail and display only the Area of Responsibility. If they do that the report won't total correctly. Can it be ifnumeric ignore else add to the total?

 

[Michael M]

Well, you can only do one or the other....
This if it is NOT numeric

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 = 1 To lr
    If [color=red]not [/color]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

 

[libby845]

Well, you can only do one or the other....
This if it is NOT numeric

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 = 1 To lr
    If [COLOR=red]not [/COLOR]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

Michael - I tried that, but it errors on the next line. x = x + Range("B" & r).Value

 

[Jonmo1]

I know I'm a little late to the party..
But I don't understand why you can't just divide the result of a standard sum by 2 ?
You said because sometimes the subtotals are hidden? That shouldn't matter, a sum function still includes hidden values in it's sum.

 

[libby845]

Believe me, dividing by 2 was the first thing I tried. The problem is that the departments and areas of responsibility are coming directly from an OLAP pivot table. However the data columns (which I need to sum) are built using a combination of keyed data, excel cube functions, and misc calculations. Since the departments are from an OLAP pivot the users can expand and collapse the rows detail. Unfortunately, when they do that the values associates with the departments are no longer included in the totals. Hence the reason I was hoping to have a macro developed that would read the values associated with the Areas of responsibility rather than the departments. It would keep the grand total accurate even if the department detail is collapsed. Sorry for all the confusion as to why I am trying to figure out how to make this work. Thanks to all who have read this and assisted with this problem.

 

[Michael M]

What error are you getting ??

 

[Michael M]

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"