# Macro to auto subtotal

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:45 PM GMT

## Question

Do you have any suggestions on a macro that will automatically sum data within a column based on its tier? For example:

```                 2010      2011    2012
```

1.0 Item A 500 600 700 1.1 250 300 350

``` 1.1.1            100      150      200
1.1.2            150      150      150
```

1.2 100 100 100

``` 1.2.1             50       50       50
1.2.1.1         25       25       25
1.2.1.2         25       25       25
1.2.2             50       50       50
```

In this example, the user would input data for the lowest tiers and the macro would automatically subtotal all tiers higher in the hierarchy. I'm currently doing this using a len() function to differentiate between the tiers, ex. 1.1.1 is 5. And then using a sum(if()) function to autosum.

Any suggestions or advice would be much appreciated!

John

Hi John How are you?

What about the following Sub abovetiers()

Dim R As Range, X As String, L As Integer Set R = ActiveSheet.Range("A3:D11") R.Cells(1, 1) = Mid(R.Cells(1, 1), 1, 1) X = InputBox("lowest tier") + ";" T = 0: P = "" I = 1 Do While Mid(X, I, 1) <> ";"

```  P = P + Mid(X, I, 1)
If Mid(X, I, 1) <> "." Then
L = 1
XFOUND = False
Do While XFOUND = False And L <= R.Rows.Count
If P = R.Cells(L, 1) Then
XFOUND = True
Else
L = L + 1
End If
```
```    Loop
For J = 2 To 4
T = T + R.Cells(L, J)
Next J
End If
I = I + 1
```

Loop MsgBox Mid(X, 1, Len(X) - 1) + "---" + Str(T) End Sub