Solved

Add number line count to match inserted lines

Posted on 2013-12-29
5
374 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 

Author Comment

by:NYANBCNY32
ID: 39745013
0
 
LVL 11

Expert Comment

by:John Easton
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 11

Accepted Solution

by:
John Easton 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

632 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