Solved Macro formula to insert lines depending on quantity field

June 28, 2014 at 21:16:18
Specs: Windows 7
Macro formula to insert lines depending on quantity field
If the quantity in Column G is >1 then I need to insert the same line one less times than the original quantity. In the example below in cell 2G the quantity = 3 so I have inserted 2 lines immediately below it and copied the text from cells 2A to 2F that I now have a total of 3 lines for this item (making labels for these items).

So the next cell requiring attention is 6G for Qty 5 (being >1) it will require 4 lines immediately under it with the same text as cells 6a to 6F.

Note that cell 9G (Qty = 2) already has a blank line under it, as it is a division between groups of items so it still requires 1 additional line with this macro added. Exactly the same requirement for cell 12G and so on...

Can any one help please with a macro to do this?

See More: Macro formula to insert lines depending on quantity field

Report •

June 30, 2014 at 06:42:50
✔ Best Answer
First, to eliminate any confusion we should make sure that we are using the correct terminology. 2 quick items:

1 - The proper way to refer to Excel cells is to use the Column first, then the Row. e.g. G6, A6:F6, etc.

2 - The term "macro formula" doesn't really mean anything. A macro is a series of VBA instructions, also known as "VBA code". A formula is built around an Excel function and entered into a spreadsheet cell. Formulas can be used within a macro, and a macro can put a formula in a cell, but the term "macro formula" is not typically used.

OK, all that said, give this code a try. I suggest that you try this in a backup copy of your workbook since macros can not easily be undone.

The code below is written based on the assumption that the data is in Sheet 1. That is easily changed.

Sub InsertRow_By_G_Value()
Dim lastRw, rw As Long
 Application.ScreenUpdating = False
'Perform actions on Sheet 1
   With Sheets(1)
'Determine last Row with data in Column G
    lastRw = .Cells(Rows.Count, "G").End(xlUp).Row
'Loop through rows in reverse order
      For rw = lastRw To 2 Step -1
'If Column G > 1, insert Rows and copy data
         If .Cells(rw, "G") > 1 Then
          For newRw = 1 To .Cells(rw, "G") - 1
'Insert Rows/Copy A-F
            .Cells(rw + 1, "G").EntireRow.Insert shift:=xlDown
            .Range(Cells(rw, "A"), Cells(rw, "F")).Copy Cells(rw + 1, "A")
         End If
   End With
 Application.ScreenUpdating = False
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Report •
Related Solutions

Ask Question