-
Aug 7th, 2020, 02:54 AM
#1
Thread Starter
Banned
How to generate an alphanumeric tree changes with criteria?
I'm working on excel sheet template used for SAP System and I have 2 columns looks like below:
Code:
Column C Column E
Level Element Code
3 ABCD.01.01.01
4 ABCD.01.01.01.01
4 ABCD.01.01.01.02
4 ABCD.01.01.01.03
3 ABCD.01.01.02
4 ABCD.01.01.02.01 'I Want to Restart Numbering Here
4 ABCD.01.01.02.02
4 ABCD.01.01.02.03
I succeeded in level 3 to be automated in the whole sheet by Macro as below
Code:
Sub AutoNumber3()
Dim Rng, C As Range
Dim Lrow As Long
Dim i As Integer
Lrow = Cells(Rows.Count, 1).End(xlUp).Row
Set Rng = Worksheets("Union").Range("C2:C" & Lrow)
i = 1
For Each C In Rng.Cells
If C.Value = 3 Then
For i = 1 To i Step 1
C.Offset(0, 2).Value = "ABCD.01.01." & i
Next i
End If
Next C
End Sub
and I used the same for level 4 as below
Code:
Sub AutoNumber4()
Dim Rng, C As Range
Dim Lrow As Long
Dim i As Integer
Lrow = Cells(Rows.Count, 1).End(xlUp).Row
Set Rng = Worksheets("Union").Range("C2:C" & Lrow)
i = 1
For Each C In Rng.Cells
If C.Value = 4 Then
For i = 1 To i Step 1
C.Offset(0, 2).Value = "ABCD.01.01.01" & i
Next i
End If
Next C
End sub
I want to Restart the numbering of level 4 from 1 each time the cells values in the level column = 3 by using Do Until the next C.Value = 3, I = 1 But I can not put it correctly in the Autonumber4 procedure
Your help is highly appreciated since this sheet may reach to 50000 or 100000 rows which is impossible to fill them manually
Thanks, Regards
Moheb Labib
-
Aug 7th, 2020, 09:21 PM
#2
Re: How to generate an alphanumeric tree changes with criteria?
That looks like Excel/VBA, right? You need to ask in the Office Development Forum
- Coding Examples:
- Features:
- Online Games:
- Compiled Games:
-
Aug 7th, 2020, 10:59 PM
#3
Re: How to generate an alphanumeric tree changes with criteria?
Originally Posted by .paul.
That looks like Excel/VBA, right? You need to ask in the Office Development Forum
I've asked the mods to move this thread to the appropriate forum. Please don't create a duplicate thread in the mean time.
-
Aug 8th, 2020, 03:20 AM
#4
Re: How to generate an alphanumeric tree changes with criteria?
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
Aug 8th, 2020, 08:01 AM
#5
Re: How to generate an alphanumeric tree changes with criteria?
you can test like
Code:
Dim cel As Range, sfirst As String, cnt3 As Integer, cnt4 As Integer
sfirst = "ABCD.01.01."
cnt3 = 0
For Each cel In Range("c:c") ' i just looped column till first empty cell, change if you prefer, any headers will be ignored as they don't match the criteria of 3 or 4
If IsEmpty(cel) Then Exit For
If cel = 3 Then
cnt3 = cnt3 + 1
cel.Offset(, 2) = sfirst & Format(cnt3, "00")
cnt4 = 0
ElseIf cel = 4 Then
cnt4 = cnt4 + 1
cel.Offset(, 2) = sfirst & Format(cnt3, "00.") & Format(cnt4, "00")
End If
Next
Code:
3 ABCD.01.01.01
4 ABCD.01.01.01.01
4 ABCD.01.01.01.02
4 ABCD.01.01.01.03
4 ABCD.01.01.01.04
4 ABCD.01.01.01.05
3 ABCD.01.01.02
4 ABCD.01.01.02.01
4 ABCD.01.01.02.02
4 ABCD.01.01.02.03
3 ABCD.01.01.03
4 ABCD.01.01.03.01
3 ABCD.01.01.04
4 ABCD.01.01.04.01
4 ABCD.01.01.04.02
4 ABCD.01.01.04.03
4 ABCD.01.01.04.04
4 ABCD.01.01.04.05
4 ABCD.01.01.04.06
4 ABCD.01.01.04.07
4 ABCD.01.01.04.08
4 ABCD.01.01.04.09
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|