Macro to auto subtotal

QuestionEdit

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

AnswerEdit

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

Cheers + excelent 2011 Adelaide