Next cell after repeat is met

[joferder]

I have a workbook that I use to create labels. It is pretty simple. There is one tab that the user would type in the last name of the person in column A and the number of labels needed in Column B.

Table Name: <mark>Teacher Names and Numbers</mark>
[table="width: 250, class: grid"]
[tr]
[td]Name[/td]
[td]Qty[/td]
[/tr]
[tr]
[td]Smith[/td]
[td]2[/td]
[/tr]
[tr]
[td]Doe[/td]
[td]3[/td]
[/tr]
[/table]


On the other tab in the workbook 'Labels", there are a couple of macros designed to repeat a teacher's name the number of times needed to for the number of labels they need and then create the labels so that I can import them into our label maker software.

[table="width: 200, class: grid"]
[tr]
[td]Smith 01[/td]
[/tr]
[tr]
[td]Smith 02[/td]
[/tr]
[tr]
[td]Doe 01[/td]
[/tr]
[tr]
[td]Doe 02[/td]
[/tr]
[tr]
[td]Doe 03[/td]
[/tr]
[/table]

The macro I am using is:

Sub RepeatData()
' hiker95, 10/29/2013
' http://www.mrexcel.com/forum/excel-questions/735863-repeat-cell-multiple-times-based-variable.html
Dim a As Variant, c As Variant
Dim i As Long, ii As Long, n As Long
a = Range("A2:B" & Range("A" & Rows.Count).End(xlUp).Row)
n = Application.Sum(Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row))
ReDim c(1 To n, 1 To 1)
For i = LBound(a, 1) To UBound(a, 1)
  If a(i, 2) <> "" Or a(i, 2) <> 0 Then
    For n = 1 To a(i, 2)
      ii = ii + 1
      c(ii, 1) = a(i, 1)
    Next n
  End If
Next i
Columns(3).ClearContents
Range("C2").Resize(UBound(c, 1), UBound(c, 2)) = c
Columns(3).AutoFit
End Sub

The problem I recently came upon was that some of our teachers already have computers in their rooms and I don't need to print out a whole new set of labels, so I would like to add a column to the first spreadsheet that would simply list the number of computers already in the room and the formula would automatically start with that number. The problem I am having is that I can get the first name to work correctly, but when it goes to the next name it starts counting at 1 because the formula automatically goes to the next person as it goes down the page. The best I can think of would be to have the macro repeat the number of computers that each teacher already has in column D until the next person's name starts and then their number of computers would repeat next to theirs. My labels page would look something like this:

Sheet Name: <mark>Labels</mark>
[table="width: 600, class: grid"]
[tr]
[td]Name[/td]
[td]# of Lbls[/td]
[td]PCs Already[/td]
[td]Name (rpt)[/td]
[td]PC already (rpt)[/td]
[td]Lbl #[/td]
[td]Label[/td]
[/tr]
[tr]
[td]Smith[/td]
[td]2[/td]
[td]6[/td]
[td]Smith[/td]
[td]6[/td]
[td]7[/td]
[td]Smith 07[/td]
[/tr]
[tr]
[td]Doe[/td]
[td]3[/td]
[td]3[/td]
[td]Smith[/td]
[td]6[/td]
[td]8[/td]
[td]Smith 08[/td]
[/tr]
[tr]
[td][/td]
[td][/td]
[td][/td]
[td]Doe[/td]
[td]3[/td]
[td]4[/td]
[td]Doe 04[/td]
[/tr]
[tr]
[td][/td]
[td][/td]
[td][/td]
[td]Doe[/td]
[td]3[/td]
[td]5[/td]
[td]Doe 05[/td]
[/tr]
[tr]
[td][/td]
[td][/td]
[td][/td]
[td]Doe[/td]
[td]3[/td]
[td]6[/td]
[td]Doe 06[/td]
[/tr]
[/table]

I hope that all of this makes sense. I would really appreciate any help that anyone can offer to get my macro to repeat the number of computers already in each room on my 'Labels' sheet from 'Teacher Names and Numbers' sheet. If any of this doesn't make sense, please let me know and I will see if I can clarify it.

Thank you in advance for any and all help.

John

 

[iggydarsa]

Sub RepeatData()
    Range("D2:G" & Rows.Count).ClearContents
    lr = Range("A" & Rows.Count).End(xlUp).Row
    counter = 2
    For i = 2 To lr
        lblCnt = Range("B" & i).Value
        increment = 1
        For j = 1 To Range("B" & i).Value
            Range("D" & counter).Value = Range("A" & i).Value
            Range("E" & counter).Value = Range("C" & i).Value
            Range("F" & counter).Value = Range("C" & i).Value + increment
            Range("G" & counter).Value = Range("A" & i).Value & " " & Format(Range("C" & i).Value + increment, "00")
            counter = counter + 1
            increment = increment + 1
            If increment > lblCnt Then Exit For
        Next j
    Next i
End Sub