Problems with auto numbering....

[kjicha]

I am having an issue when the numbering with my macro, when I get to 1.9 after it is 1.9 and it goes to the next row it just puts 1.1 It needs to be 1.10 Same when it gets to 1.19 it just goes to 1.2 instead of 1.20.

Any ideas?

Below is my code:

Sub WBSNumbering()
'From http://j.modjeska.us/?p=31
'Renumber tasks on a project plan
'Associate this code with a button or other control on your spreadsheet
'Layout Assumptions:
'Row 1 contains column headings
'Column A contains WBS numbers
'Column B contains Task description, with appropriate indentation
'Some text (here we assume "END OF PROJECT") delimits the end of the task list
On Error Resume Next
'Hide page breaks and disable screen updating (speeds up processing)
Application.ScreenUpdating = False
ActiveSheet.DisplayPageBreaks = False
'Format WBS column as text (so zeros are not truncated)
ActiveSheet.Range("A:A").NumberFormat = "#"
Dim r As Long 'Row counter
Dim depth As Long 'How many "decimal" places for each task
Dim wbsarray() As Long 'Master array holds counters for each WBS level
Dim basenum As Long 'Whole number sequencing variable
Dim wbs As String 'The WBS string for each task
Dim aloop As Long 'General purpose For/Next loop counter
r = 3 'Starting row
basenum = 0 'Initialize whole numbers
ReDim wbsarray(0 To 0) As Long 'Initialize WBS ennumeration array
'Loop through cells with project tasks and generate WBS
Do While Cells(r, 3) <> ""
'Ignore empty tasks in column B
If Cells(r, 3) <> "" Then
'Skip hidden rows
If Rows(r).EntireRow.Hidden = False Then
'Get indentation level of task in col B
depth = Cells(r, 3).IndentLevel
'Case if no depth (whole number master task)
If depth = 0 Then
'increment WBS base number
basenum = basenum + 1
wbs = CStr(basenum)
ReDim wbsarray(0 To 0)
'Case if task has WBS depth (is a subtask, sub-subtask, etc.)
Else
'Resize the WBS array according to current depth
ReDim Preserve wbsarray(0 To depth) As Long
'Repurpose depth to refer to array size; arrays start at 0
depth = depth - 1
'Case if this is the first subtask
If wbsarray(depth) <> 0 Then
wbsarray(depth) = wbsarray(depth) + 1
'Case if we are incrementing a subtask
Else
wbsarray(depth) = 1
End If
'Only ennumerate WBS as deep as the indentation calls for;
'so we clear previous stored values for deeper levels
If wbsarray(depth + 1) <> 0 Then
For aloop = depth + 1 To UBound(wbsarray)
wbsarray(aloop) = 0
Next aloop
End If
'Assign contents of array to WBS string
wbs = CStr(basenum)
For aloop = 0 To depth
wbs = wbs & "." & CStr(wbsarray(aloop))
Next aloop
End If
'Populate target cell with WBS number
Cells(r, 2).Value = wbs
'Get rid of annoying "number stored as text" error
Cells(r, 2).Errors(xlNumberAsText).Ignore = True
'Apply text format: next row is deeper than current
If Cells(r + 1, 3).IndentLevel > Cells(r, 3).IndentLevel Then
Cells(r, 2).Font.Bold = False
Cells(r, 3).Font.Bold = False
'Else (next row is same/shallower than current) no format
Else
Cells(r, 2).Font.Bold = False
Cells(r, 3).Font.Bold = False
End If

'Special formatting for master (whole number) tasks)
If Cells(r, 3).IndentLevel = 0 Then
Cells(r, 2).Font.Bold = True
Cells(r, 3).Font.Bold = True
End If

'Formatting color of row with no indents
If Cells(r, 3).IndentLevel = 0 Then
Cells(r, 1).Interior.ColorIndex = 48
Cells(r, 2).Interior.ColorIndex = 48
Cells(r, 3).Interior.ColorIndex = 48
Else
If Cells(r, 3).IndentLevel = 1 Then
Cells(r, 1).Interior.ColorIndex = 15
Cells(r, 2).Interior.ColorIndex = 15
Cells(r, 3).Interior.ColorIndex = 15
Else

If Cells(r, 3).IndentLevel > 1 Then
Cells(r, 1).Interior.ColorIndex = 0
Cells(r, 2).Interior.ColorIndex = 0
Cells(r, 3).Interior.ColorIndex = 0
End If
End If
End If


End If
End If

'Go to the next row
r = r + 1
Loop
End Sub


Additional coding:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo quit
Application.EnableEvents = False
With Target
'If .Count = 1 Then
If .Column = 3 Then
.WrapText = True
'If .Offset(0, -2).Value = "" Then .Offset(0, -2).Value = Range("A1") + 1
End If
'End If
End With
quit:
Application.EnableEvents = True
End Sub

 

[Rorylfc]

1.1 is effectively 1.10 and 1.2 is effectively 1.20.... it is just not showing as 2 decimal places.

For reference i assume you are seeing these numbers on the spreadsheet. If so just format to show as 2 decimal places.

 

[Joe4]

If you want to keep them as numbers, and have the right number of decimals and have them sort correctly, you can should use:

1.01
1.02
1.03
...
1.09
1.10

If you want do it like:

1.1
1.2
1.3
...
1.9
1.10

You will need to format them as Text and not numbers. But note that they will not sort correctly, as 1.10 is less than 1.2.

 

[Logit]

I may not fully understand the goal here, but if you format the cells as NUMBER with two numbers after the decimal, it works as desired.

I formatted Column G as NUMBER with decimal and two digits after.
Entered 1.00 in G1
Entered 1.01 in G2

Highlighted both and drag down the column. All subsequent numbers formatted and resulted as I understand you want.

????

 

[kjicha]

so where in the code are you suggesting I change/add this? I want to keep it as the bottom pic you show

 

[kjicha]

I do not want all of them to be 1.01 1.02 it should be 1.1, 1.2, .... 1.9, 1.10. 1.11 ....

 

[Joe4]

Where did this code come from?
(By the way, when posting Code, please use the Code Tags, otherwise you lose all indenting and your code becomes very difficult to read. See here: https://www.mrexcel.com/forum/board...post-your-visual-basic-applications-code.html)

Also, can you tell us how exactly you are using this?
What are you entering in to your sheet and where?

Probably the best way to see what is going on and how to change it would be for us to try to emulate what you are doing.

 

[kjicha]

The code came from: http://j.modjeska.us/?p=31

I have 3 columns, A, B, and C

In column B I have a macro with the code above that does the formatting and auto numbering. Column C is the requirement and if the text is indented or not it does different formatting and numbering based off of that. (again code above).

Example: First row has data in it. Column C for this row the text isn't indented there for it shows a 1 in column B. Next row, in column C there is text that is indented once, so column B shows 1.1. This goes for a while and when I get to 1.10, it only show 1.1 and then the next row will show 1.11 I need it to show 1.10 instead of 1.1. Same would go for after 1.19, I need it to show 1.20 instead of 1.2

Hope this helps.

 

[Joe4]

It looks like you may some changes to the code.

The problem is that you changed this part:

    'Format WBS column as text (so zeros are not truncated)
    ActiveSheet.Range("A:A").NumberFormat = "@"

to this:

    'Format WBS column as text (so zeros are not truncated)
    ActiveSheet.Range("A:A").NumberFormat = "#"

which changes all those entries from text to numbers, which will drop any trailing zeroes.

 

[kjicha]

I just changed it back to what it was and that didn't fix the issue.