Solved

Add number line count to match inserted lines

Posted on 2013-12-29
5
358 Views
Last Modified: 2013-12-30
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.
0
Comment
Question by:NYANBCNY32
  • 3
  • 2
5 Comments
 

Author Comment

by:NYANBCNY32
ID: 39745013
0
 
LVL 10

Expert Comment

by:JEaston
ID: 39745040
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

0
 

Author Comment

by:NYANBCNY32
ID: 39745272
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.
0
 
LVL 10

Accepted Solution

by:
JEaston earned 500 total points
ID: 39745846
I think this was an error in the original formula which I didn't spot.  The below should deal with rows where Recordings equal 1 now:

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

Open in new window

0
 

Author Closing Comment

by:NYANBCNY32
ID: 39745929
Excellent! Thank you for the help on this piece.
0

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question