vba loop based on cell value

Question

Hello,

I want to be able to create a list based on my sheet 'output' on values from the 'list' sheet. The complication is that the list sheet contains a value in column B that I want to use to define the number of rows allocated to that record when it is created on the 'output' sheet. The image below shows my starting 'list' data and what I want on my 'output' sheet:

Any help would be awesome.

Cheers,

Mark

Answer 1

For the worksheets and data as shown:

Sub test()

    Dim rngC As Range

    Dim wsS As Worksheet

    Dim wsT As Worksheet

    Dim i As Integer

    Dim lngR As Long

    Set wsS = Worksheets("List")

    Set wsT = Worksheets("Output")

    For Each rngC In wsS.Range(wsS.Range("B2"), wsS.Cells(wsS.Rows.Count, "B").End(xlUp))

        For i = 1 To rngC.Value

            With wsT

                lngR = .Cells(.Rows.Count, "A").End(xlUp).Row + 1

                .Cells(lngR, "A").Value = wsS.Range("H1").Value

                .Cells(lngR, "B").Value = rngC.Offset(0, -1).Value

            End With

        Next i

    Next rngC

End Sub

Answer 2

Bernie, you're the MAN.

Thanks a lot, that's going to save me heaps of time.

Cheers,

Mark