Looping through arrays to generate a list

Question

I want to generate a list of names for a ticket raffle.

I would generate a name x amount of times, for how many tickets they have bought, and add a unique identifier on the end.

For example, if Pikachu bought 40 tickets, the code would generate "Pikachu1", "Pikachu2", etc.

Pikachu1
Pikachu2
.
.
.
Pikachu40
Bulbasaur1
Bulbasaur2
Bulbasaur3
.
.
.
Bulbasaur20

I have an array for the names and an array for the numbers of tickets. I'm thinking to loop through the array of names, generate the name numberOfTickets time, add the unique identifier, and so on.

Sub raffleTickets()
    
    Dim totalRaffleTickets As Variant
    Dim nNames As Variant
    Dim y As Integer
    
    totalRaffleTickets = Range("$I5:$I135").Value
    nNames = Range("$A5:$A135").Value
    
    For y = LBound(nNames) To UBound(nNames)
            ...
    Next y
    
End Sub

Previously I hard coded it so I got the name from the cell and then printed it x many of times.

Answer 1

• Use a nested loop to generate the list
• Write the output to a new worksheet (modify as needed)

Microsoft documentation:

ReDim statement

Range.Resize property (Excel)

Option Explicit

Sub raffleTickets()
    Dim totalRaffleTickets As Variant
    Dim nNames As Variant
    Dim y As Long, x As Long, arrRes, iR As Long, iTotal As Long
    totalRaffleTickets = Range("$I5:$I135").Value
    nNames = Range("$A5:$A135").Value
    ' get the total tickets
    iTotal = Application.Sum(Range("$I5:$I135"))
    ReDim arrRes(1 To iTotal, 0)
    iR = 0
    ' loop throught names
    For y = LBound(nNames) To UBound(nNames)
        If totalRaffleTickets(y, 1) > 0 Then
            For x = 1 To totalRaffleTickets(y, 1)
                ' populate array
                iR = iR + 1
                arrRes(iR, 0) = nNames(y, 1) & CStr(x)
            Next x
        End If
    Next y
    ' write output to a new sheet
    Sheets.Add
    Range("A1").Resize(iR, 1) = arrRes
End Sub