Help with ReDim

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.

hi c.young, option

Sub GenerateNewSerialNumbers()
    
    Const strModel As String = "Model B "
    Const strEnd As String = "-B-1"
    
    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 = Replace(Replace(Range("G20"), strModel, ""), strEnd, "")
    
    For rIndex = 1 To 80
        For cIndex = 1 To 7 Step 2
            nSerial = nSerial + 1
            arrNew(rIndex, cIndex) = strModel & nSerial & strEnd
        Next cIndex
    Next rIndex
    
    Range("A1:G20").Value = arrNew
    
End Sub

Watersev,

I get a type mismatch when I run that.

I have attached the old version and the new version of the spread sheets.Model B Warranty Labels.xlsm UL Model B Warranty Labels.xlsm

The new serial number format is in the UL spreadsheet.

your original post stated "Model B " and now it appears to be "MODEL B ", change constant strModel in the code and you will get expected result

Sorry to miss that small error. Yes, the script now works, thank you.