I have a spreadsheet that includes a script to generate serial numbers. The script looks at the last s/n in the range and the rewrites the range with the next series of numbers. The old serial number format used to look like this "Model B 123456" but the numbers have now gone to the format "Model B 1234-B-1" with the "-B-1" remaining the same for each serial number.

Sub GenerateNewSerialNumbers()
    
    Const strModel As String = "Model B "
       
    Dim arrNew() As String
    Dim nSerial As Long
    Dim rIndex As Long
    Dim cIndex As Long
    
    ReDim arrNew(1 To 80, 1 To 7)
    nSerial = --Mid(ActiveSheet.Range("G20").Text, InStrRev(ActiveSheet.Range("G20").Text, " "))
    
    For rIndex = 1 To 80
        For cIndex = 1 To 7 Step 2
            nSerial = nSerial + 1
            arrNew(rIndex, cIndex) = strModel & nSerial
        Next cIndex
    Next rIndex
    
    Range("A1:G20").Value = arrNew
    
End Sub

I have tried to change this script to get it to work, but there is something happening that I am not understanding. I think it might be in the ReDim section of the script.

Could someone please point me in the right direction so I can get this script working again for the new serial number? I realize there are simpler ways to do this, but we have some people who arent too computer savvy so it works well for them to be able to open the spreadsheet, click the "Generate New Serial Numbers" button and then print.

Thanks.