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
Cheers + excelent 2011 Adelaide
Advertisement