creating multiple data rows, for labels based on a cell value

josbor01 · #1 01-02-2018, 08:09 AM

I have a need to produce multiple labels (probably via a mail merge (or something). my sticking point is that the number of labels will vary based on a cell value for the given row(i.e quantity)

I am assuming there is a way within a loop or some other method, but I am not finding such an example in my searches. currently, all of the data is in Excel-but could export to Access if a solution within Access is needed for more in-depth code
Sample data below with some explanation

item quantity # of labels needed( quantity*2)
101 1 2
102 5 10

NoSparks · #2 01-02-2018, 08:51 AM

Assuming items start in A2 with quantity in column B,
you could produce a list of all the labels needed in another column for mail merge (or something.)

Code:

Sub test_1()
    Dim rng As Range, cl As Range
    Dim i As Integer, wr As Long  '<~~ wr is write row

With Sheets("Sheet1")  '<~~ change sheet name to suit
    Set rng = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
    wr = 2  '<~~ row to start writing on
    For Each cl In rng
        For i = 1 To cl.Offset(, 1).Value * 2
            .Cells(wr, 5).Value = cl.Value  '<~~ column E is 5
            wr = wr + 1
        Next i
    Next cl
End With
End Sub

josbor01 · #3 01-02-2018, 10:59 AM

Thanks, that helps; but of course, need a little more help with the range and to pull out specific columns. I have provided additional columns and data elements

input data: {sheetX}
Item{A} Description{B} data1{D} data2{G} quantity{J} Loc1{K} loc2{L}
101 Description101 data101_1 data101_2 1 M1 C1
102 Description102 data102_1 data102_2 2 M1 C2

needed/expected output: {label_data}
Item{A} Description{B} data1{C} data2{D} quantity{E} Loc1{F} loc2{G}
101 Description101 data101_1 data101_2 1 M1 C1
101 Description101 data101_1 data101_2 1 M1 C1
102 Description102 data102_1 data102_2 2 M1 C2
102 Description102 data102_1 data102_2 2 M1 C2
102 Description102 data102_1 data102_2 2 M1 C2
102 Description102 data102_1 data102_2 2 M1 C2

NoSparks · #4 01-02-2018, 02:04 PM

Code:

Sub SecondLocationOfGoalPosts()

Dim rng As Range, cl As Range
Dim wr As Long, i As Integer

Set rng = Sheets("sheetx").Range("A2", Sheets("sheetx").Range("A" & Rows.Count).End(xlUp))
wr = 2

With Sheets("label_data")
    For Each cl In rng
        For i = 1 To cl.Offset(, 9).Value * 2
            .Cells(wr, 1) = cl
            .Cells(wr, 2) = cl.Offset(, 1)
            .Cells(wr, 3) = cl.Offset(, 3)
            .Cells(wr, 4) = cl.Offset(, 6)
            .Cells(wr, 5) = cl.Offset(, 9)
            .Cells(wr, 6) = cl.Offset(, 10)
            .Cells(wr, 7) = cl.Offset(, 11)
            wr = wr + 1
        Next i
    Next cl
End With

End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Extracting multiple words from one cell into individual rows while copying all other data	randyaserve	Excel Programming	4	10-05-2015 09:52 AM
Hide rows in multiple columns based on zero values	Deane	Excel Programming	19	06-23-2015 11:24 PM
Insert values from multiple rows based on value in one column	pachmarhi	Excel	3	07-18-2014 09:57 PM
Creating formula based on if data is correct in cell	MattMurdock	Excel	1	08-06-2012 03:11 AM
Hide Rows and Update Chart based on cell value	ubns	Excel Programming	5	05-07-2012 05:44 AM