Outlining based on level numbers in most left column

[Werner Rohrmoser]

Hi,

I have a table, which is downloaded from SAP and has no formulas.
The identifier of the rows is a project item number like "PRD.
00133.07-03-04" and
I count the number of "." and "-" with a formula to get the level of
the project item.
My goal is now to make an outline by vba using the level numbers, e.g.
4 in the case
described above.
The first row of the table is poulate with these level numbers like
1
2
2
3
3
2
1
2 and so on.

Does anyone have a vba procedure to solve this problem.
Thanks.

Excel XP SP3
Windows XP SP3

Best Regards
Werner

 

[Dave Peterson]

First, this needs xl2k or higher to run. It uses Replace that was added in
xl2k.

Second, I'm not sure how your data is laid out, but I created a test worksheet
with the data to be examined in column B. And I plopped the numbers into column
A. So don't use this without modifying the code or your data if it doesn't
match!

Option Explicit
Sub testme02()
Dim myCell As Range
Dim myRng As Range
Dim wks As Worksheet
Dim HowManyChars As Long
Dim WhatChars As Variant
Dim cCtr As Long

WhatChars = Array(".", "-")

Set wks = ActiveSheet

With wks
Set myRng = .Range("b1", .Cells(.Rows.Count, "B").End(xlUp))

For Each myCell In myRng.Cells
HowManyChars = 0
For cCtr = LBound(WhatChars) To UBound(WhatChars)
HowManyChars = HowManyChars _
+ (Len(myCell.Value) _
- Len(Replace(expression:=myCell.Value, _
Find:=WhatChars(cCtr), _
Replace:="", _
compare:=vbTextCompare))) _
/ Len(WhatChars(cCtr))
Next cCtr
myCell.Offset(0, -1).Value = HowManyChars
Next myCell
End With

End Sub

 

[Rick Rothstein]

Assuming I understand your counting method (more examples would have been
helpful), I believe this macro will do what you want...

Sub LevelNumbers()
Dim Cell As Range
For Each Cell In Range("B2", Cells(Rows.Count, "B").End(xlUp))
Cell.Offset(0, -1).Value = UBound(Split(Replace(Cell.Value, _
".", "-"), "-")) - (Cell.Value = "")
Next
End Sub

Note that I assume this code is being run on the ActiveSheet and that your
ID numbers are in Column B starting in Row 2 (adjust the B2 references in
the Range call and the "B" column reference in the Cells call, both in the
For..Each statement as needed) and the level numbers are being placed in the
preceding column (that's what the -1 is doing in the Offset call).

 

[Werner Rohrmoser]

Oh, I'm sorry, I've described my problem insufficient.
My problem is not to count the numbers of "-" and ".", because this
ismade by a formula
which goes like this: {=SUM((MID(SUBSTITUTE(B6,"-","."),COLUMN(1:1),
1)=".")*1)}.

My table is organised by column this way:
Level Criteria_A Criteria_B ........ Criteria_N.

So, based on the level (1, 2, 3,..., N) I'd like to make the same
which I have to do now
manually with the command "Data" => "Group and Outline" => "Group".


Example for the table:

Level Crit A Crit B

1 PRD.00133 Crit 1
2 PRD.00133.01 Crit 2
2 PRD.00133.04 Crit 3
2 PRD.00133.05 Crit 4
2 PRD.00133.06 Crit 5
2 PRD.00133.07 Crit 6
3 PRD.00133.07-02 Crit 7
3 PRD.00133.07-03 Crit 8
4 PRD.00133.07-03-01 Crit 9
4 PRD.00133.07-03-02 Crit 10
4 PRD.00133.07-03-03 Crit 11
2 PRD.00133.08 Crit 12
2 PRD.00133.10 Crit 13
2 PRD.00133.11 Crit 14

I hope my definition of the problem is now better, there are always
problems
as a non native speaker, sorry once more.

Best Regards
Werner