How to do sequencing number after rows duplicated

[VISHAL120]

Hi everyone,

I am actually using this below code to duplicate rows based on the cell value. Which in this case is the column AC which represents number of operators.
Here is the code I am using which works perfectly to duplicate the rows based on the Column AC[ number of operators].
Code


Sub populate_operations_sequence()




Dim lngRow As Long
'start_Time = Time
Dim intInsertRows As Integer
Transfer_Header_Row = Range("shadow_header_row").Row + 1
'Transfer_Number_Populate_Col = Range("Transfer_Number_Populate_Col").Column


Sheets("SHADOW1").Select
On Error Resume Next
Application.ScreenUpdating = False

'MsgBox ("Please Recheck before Populating, if you are sure then click YES"), vbYesNo, ""
'If Ans = vbYes Then
lngRow = Transfer_Header_Row
Do Until IsEmpty(Range("AC" & lngRow))
If Range("AC" & lngRow).Value > 1 Then
intInsertRows = Range("AC" & lngRow).Value - 1
Range("AC" & lngRow + 1 & ":AC" & lngRow + intInsertRows).EntireRow.Insert
Range("A" & lngRow & ":AC" & (lngRow + intInsertRows)).FillDown
Range("AD" & lngRow & ":AD" & (lngRow + intInsertRows)).Value = 1
lngRow = lngRow + intInsertRows
End If
lngRow = lngRow + 1

Loop
'End If
Call FORMULADATA


End Sub


The data on sheet [SHADOW1] is not yet duplicated. You can click on the button [ click to duplicate to see ] on the attached file.


My only problem I am having is for the required operation sequence number which is in column A because whenever in Column AC for example in row 4 the duplicate value [ the operator required] is 3. It duplicates it 2 times as 1 time is already existing and then the sequence in Column A shall become 2,3,4 instead of 2,2,2.

But also, it has to check in column B where If the poste is repeated it shall continue with the same number for example after the duplicate from row 4 It became 2,3,4,5,6,6,6,7 it’s because from cell B8 to B10 the poste is 4,4,4. Please see attached file on sheet [data after duplicate] for better understanding.


Right now, I am doing this manually. Can someone help how we can set the sequencing after the duplication is completed.
Sheet [data after duplicate] shows exactly what is needed after the duplication.

Thanks in advance for your support.

[snb]

Simply use:


Sub M_snb()
sn = Sheet2.Cells(2, 1).CurrentRegion.Resize(, 30)

With CreateObject("scripting.dictionary")
For j = 1 To UBound(sn)
If j > 1 Then sn(j, 30) = 1
If j > 1 Then sn(j, 1) = .Count
.Item(.Count) = Application.Index(sn, j)
For jj = 2 To Val(sn(j, 29))
If j > 1 Then sn(j, 1) = .Count
.Item(.Count) = Application.Index(sn, j)
Next
Next

Sheet1.Cells(70, 1).Resize(.Count, UBound(sn, 2)) = Application.Index(.items, 0, 0)
End With
End Sub

NB. The second macro is redundant now.
NB1. Avoid worksheet interaction as much as possible.
NB2. Always use row 1 and column A in every worksheet.
NB3. Never use 'Select' or 'Activate' in VBA

PS. why don't you use dynamic tables ?

[VISHAL120]

Hi Snb,

thank you for your help Sir.

I have tested the code several times on the sheet but its not working can you please guide me . how to make it work .

for dynamic table: actually the sheet fetches data from other sheet for analysis and then from here is send again to other sheets for final analysis. that is why i have not yet tested dynamic tables.

[snb]

Ditd you look in Row 70 ?

[VISHAL120]

Hi Snb,

So sorry i did not look at that. Yes i saw it but there are some anomalies i saw like:

1. for the poste 2 the duplicate is ok
2. for poste 10 also its ok
3. poste 29 if you see it have done it 4 times instead shall be 3 times.
4. for column X the data has been converted to date like shall be 9-11 its showing 11-sep..

in fact the data shall be shown on the sheet Shadow1 itself as the sheet data after duplicate was given as example.

if possible if you can use the sheet name on the code it will be good and helpful as this is part of a system we are working and we have a lot of sheets in it.

thanks you again Sir for your time and help.

[VISHAL120]

And also for the poste 4 it has change it to 6,7,8 instead of keeping it to 6,6,6 as the number of operators in column AC is 1.

normally all poste where the operators is above 1, the required operation sequence number shall increment and increase on the other poste required operation sequence number

[snb]

I simplified the code so you will be able to adapt/maintain it yourself:


Sub M_snb()
sn = Sheet2.Cells(2, 1).CurrentRegion.Resize(, 30)

With CreateObject("scripting.dictionary")
.Item(.Count) = Application.Index(sn, 1)
For j = 2 To UBound(sn)
For jj = 1 To Val(sn(j, 29))
sn(j, 30) = 1
sn(j, 1) = .Count
.Item(.Count) = Application.Index(sn, j)
Next
Next

Sheet1.Cells(70, 1).Resize(.Count, UBound(sn, 2)) = Application.Index(.items, 0, 0)
End With
End Sub

The Value 0 in column AC seems to me to be a mistake.

[VISHAL120]

I simplified the code so you will be able to adapt/maintain it yourself:


Sub M_snb()
sn = Sheet2.Cells(2, 1).CurrentRegion.Resize(, 30)

With CreateObject("scripting.dictionary")
.Item(.Count) = Application.Index(sn, 1)
For j = 2 To UBound(sn)
For jj = 1 To Val(sn(j, 29))
sn(j, 30) = 1
sn(j, 1) = .Count
.Item(.Count) = Application.Index(sn, j)
Next
Next




Sheet1.Cells(70, 1).Resize(.Count, UBound(sn, 2)) = Application.Index(.items, 0, 0)
End With
End Sub

The Value 0 in column AC seems to me to be a mistake.

HI Snb ,

Thanks a lot for your help and time.

I have tested the code and attached the file again as data002 Sir.

Concerning the value 0 in column AC actually its not a mistake see the example below for poste 4:

the list of operations to do by the operator are as follows:
4.Pointer p° faux plaquette devant
4.Plier et surpiquer faux plaquette devant
4.Fixer haut et bas faux plaquette devant





and as you can see the operator assign is in cell AC8 as the other one are 0 this because all these operations will be done by the same operator that is why the other operations descriptions are 0 otherwise if we add 1 to each of them then it will assign each of these operations to other operators.
Thus when we print we give to the production they know exactly what operation the operator need to do and also the time it shall take. And the can follow the operation sequence also.


After running the code i see it has remove all these operations where its was 0 see the sheet [data after duplicate] when i have painted them to yellow. It has remove all the other operations descriptions where it was 0 whereas these shall be keep.
these operations number sequence was remove :
4,4,4,12,12,12,13,13,14,14,15,15,15,16,16,16,17,17,17,20,20,25,25,25,26,2

for the sequence its working fine but only if we can just solve this part that is it shall not remove the operations description where there is 0

thanks again for you help and time sir.

[snb]

Sub M_snb2()
sn = Sheet2.Cells(2, 1).CurrentRegion.Resize(, 30)

With CreateObject("scripting.dictionary")
.Item(.Count) = Application.Index(sn, 1)
For j = 2 To UBound(sn)
For jj = 1 To IIf(sn(j, 29) = 0, 1, sn(j, 29))
sn(j, 30) = 1
sn(j, 1) = .Count
.Item(.Count) = Application.Index(sn, j)
Next
Next

Sheet1.Cells(70, 1).Resize(.Count, UBound(sn, 2)) = Application.Index(.items, 0, 0)
End With
End Sub

[VISHAL120]

Hi Snb,

thank you a lot its working fine.

i have still not really understand how its doing it as i always wanted to understand the scripting.dictionary concept but has not really been able as we neve see what really its doing when debugging it line by line. BUt thank you again for that i can now proceed with the other process of the system.

Have a nice day Sir.

[snb]

Especially written for you:

https://www.snb-vba.eu/VBA_Dictionary_en.html

[VISHAL120]

Hi Snb,

thank you so much i will go through for sure.

have a nice day Sir.

[VISHAL120]

Hi Snb,

after checking it throughly i see where the operators is 1 like poste 4 it shall have been since it is 3 times 4,4,4 it shall have been 6,6,6 otherwise if it get increment as it is actually, the other operations will be assign to operators.


can we correct this part please.
Attached is the file on sheet [data after duplicate] on column J i have shown how it shall be.