Solved

Add number line count to match inserted lines

Posted on 2013-12-29
5
332 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
Comment Utility
0
 
LVL 10

Expert Comment

by:JEaston
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Excellent! Thank you for the help on this piece.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now