For each value in column A, how can I incrementally create 9 URLS in column BC with the value from column A appended to the end?

I have the following Excel spreadsheet where

• Row 1 is the header row;
• Column A is any string; and
• Column B is an empty string.

My macro below is supposed to take the value in cell A2 ('aa'), append it to a URL ('https://test.com/aa'), and then append a number ranging from 1 to 9 followed by the string '.webp'. An example output would be 'https://test.com/aa-1.webp'.

For every string in column A, it should be duplicated 9 times. In addition, each duplicated value will be accompanied by a sequential URL in column B (see table for example).

The Problem:

The desired output:

What the macro instead produces:

Link to screenshot of spreadsheet showing the problem

• The macro successfully creates 9 URLs sequentially in column B, but it starts in cell B3 instead of B2.
• The macro needlessly leaves B2 entirely blank.
• The macro leaves the IDs in column A entirely untouched. In my example, you can see that each URL in column B is supposed to be paired with its corresponding value from column A.

What I've attempted so far:

Sub DuplicateCellsAndGenerateURLs()
    Dim lastRow As Long
    Dim sourceRange As Range
    Dim destinationRange As Range
    Dim i As Long
    Dim j As Long
    
    ' Set the range of the source data in column A
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row ' This line will find the last row
    Set sourceRange = Range("A2:A" & lastRow) ' Define the source range
    
    ' Set the destination range in column A and B
    Set destinationRange = Range("A2:B" & (lastRow * 9) + 1) ' Adjust the destination range
    
    ' Duplicate values in column A
    sourceRange.Copy destinationRange.Columns(1)
    
    ' Generate URLs in column B
    i = 2 ' Start index for URL generation
    For Each cell In sourceRange
        If cell.Value = "" Then Exit For ' Stop if an empty cell is encountered in column A
        For j = 0 To 8
            destinationRange.Cells(i + j, "B").Value = "https://test.com" & cell.Value & "-" & j + 1 & ".webp"
        Next j
        i = i + 9 ' Increment index for next URL generation block
    Next cell
End Sub

TL;DR: string 'aa' should occupy A2:A10; 'https://test.com/aa-#.webp' should occupy B2:B10; repeat for each value in column A ('ab', 'ac', 'ad', etc).

The challenge is that you are overwriting your sourceRange (column A) at the same time as you want to get information from it. sourceRange will be updated when you do your loop, since it's a range(). Therefore we loose the initial data values.

One way to solve this is to store sourceRange values temporary, in another column.

Another is to store your values in a array so you prevent overwriting the "source data". Array is not updated if you don't explicitly command it to.

Sub DuplicateCellsAndGenerateURLs2()
    Dim lastRow As Long
    Dim sourceRange As Range
    Dim destinationRange As Range
    Dim lrow_bc As Long
    Dim i As Long
    Dim j As Long
    Dim k As Long
    Dim myArr As Variant
    
    ' Set the range of the source data in column A
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row ' This line will find the last row
    myArr = Application.Transpose(Range("A2:A" & lastRow)) ' Define the ID's to be store in an array
    
    j = 1 'set the intial array value (which array number loop should start from)
    For Each myCell In myArr 'Loop through the array
        If IsEmpty(myCell) Then Exit For ' Stop if an empty cell is encountered in the array (from column A)
            lrow_bc = Cells(Rows.Count, "BC").End(xlUp).Row 'Get the last row for the URL destination, update it for every ID
            For k = 1 To 9
                Cells(lrow_bc + k, "BC").Value = "https://exampleimagesource.blob.core.windows.net/inventory/" & myArr(j) & "-" & k & ".webp" 'To get Array number, use myArr(j)
            Next k
            Range("A" & lrow_bc + 1 & ":" & "A" & lrow_bc + 9).Value = myArr(j) 'Populate the ID by nine rows each time.
            j = j + 1 'update array iteration number
    Next myCell

End Sub