asked on

Add number line count to match inserted lines

The attached report has a macro that was something I received previous help on, but now I have a secondary issue. The Macro reads the amount of recordings in Column C, it then copies and inserts the number of lines.

My question is, is there a way to have Column E read column C as well and then after the lines are inserted it inserts into numeric order. So if column C reads three, in Column E after the Macro is done or during it, it would enter into Column E, 0010, 0020, 0030, or if more than 9 lines would do 0090, 0100, 0110, etc.

Please let me know if this is possible, and any help is always greatly appreciated.

NYANBCNY32

ASKER

InsertExample.xlsm

John Easton

There may be a more elegant method, but the below should do as you describe. Replace your existing macro with the below and then run:

Sub CopyRows()
Dim c, i, r, l As Integer
Dim Ind

c = 0
i = Range("C2").Value
r = 2

  For rc = 1 To ActiveSheet.UsedRange.Rows.Count - 1
    If i > 1 Then
      Ind = 1
      For l = 1 To i - 1
        Rows(r + c).Select
        Selection.Copy
        Rows(r + c + 1).Select
        Selection.Insert Shift:=xlDown
        Application.CutCopyMode = False
        Range("E" & r + c).Value = "'" & Format(Ind * 10, "0000")
        Ind = Ind + 1
        c = c + 1
      Next l
        Range("E" & r + c).Value = "'" & Format(Ind * 10, "0000")
        i = Range("C" & r + c + 1).Value
      End If
    c = c + 1
  Next rc
End Sub

Open in new window

NYANBCNY32

ASKER

This is working for all records unless there is one "1" entry, is there a way to also include that if there's 1 record then it will continue and put "0010" in column E. Right now it doesn't move if the first entry is a 1, or if an entry down further is a 1 it stops.

ASKER CERTIFIED SOLUTION

John Easton

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

NYANBCNY32

ASKER

Excellent! Thank you for the help on this piece.