2d array redim problem

[ToExcelOrNotToExcel]

Hello everyone I hav somee code which shold work but it is returning an error code 9 and I have no idea why. It's supposed to loop through a range of cells with strings that look like this: "AM17, 12: ;HM2; AM40; AM45, 13". I'm sure the code works but I don't understand how to solve this error.

Option Explicit
Sub FillPlayerRating()

    Dim rng As Range, cell As Range
    Dim ArrayCount As Integer
    Dim i As Long
    Set rng = Range("CE3:CE24")
    Dim size_1d As Integer
    
        For Each cell In rng
        Dim arr_1d() As String
        arr_1d = Split(cell.Value, ";")
        size_1d = UBound(arr_1d) - LBound(arr_1d)
        
        Dim arr_2d() As String
        ReDim arr_2d(size_1d, 1) As String
            
            For i = 0 To size_1d
            Dim temp() As String
            temp = Split(arr_1d(i), ",")
            Dim size_temp As Integer
            size_temp = UBound(temp) - LBound(temp) + 1
            If size_temp = 1 Then
                arr_2d(i, 0) = Trim(temp(0))
            ElseIf size_temp = 2 Then
                arr_2d(i, 0) = Trim(temp(0))
                arr_2d(i, 0) = Trim(temp(1))
            End If
            
            Next i
        
        ' Range("CS20").Value = arr()
        Next cell

End Sub

 

[6StringJazzer]

Which line of code is causing the error? When the error occurs click Debug and tell us which line is highlighted. What is the error description?

What do you want your code to do?

I'm sure the code works

If you are getting a runtime error, the code does not work.

 

[ToExcelOrNotToExcel]

Which line of code is causing the error? When the error occurs click Debug and tell us which line is highlighted. What is the error description?

What do you want your code to do?


If you are getting a runtime error, the code does not work.

The line that says Redim arr_2(size_1d,1) as string
It is giving me an error code 9

 

[johnnyL]

If you have a blank cell in Range("CE3:CE24"), that could explain the error.

When you get the error, click debug in the error window and then hover your mouse over the size_1d variable and tell us what value it shows.

 

[6StringJazzer]

Not possible to test/diagnose without some sample data. Also, all variables should be declared.

johnnyL is asking the right question, I eagerly await the answer.

 

[Domenic]

Check the value of size_1d when ReDim'ing. You'll likely see that it's a negative number, hence the error.

 

[johnnyL]

@Domenic you are spoiling the surprise.

 

[Domenic]

@Domenic you are spoiling the surprise.

 

[6StringJazzer]

Check the value of size_1d when ReDim'ing. You'll likely see that it's a negative number, hence the error.

That would certainly cause the error but how does Split return an array with a UBound that is lower than LBound?

 

[johnnyL]

That would certainly cause the error but how does Split return an array with a UBound that is lower than LBound?

A: It doesn't.

If arr_1d is just a variable, not an array, that is what will cause the issue.

Edit: @Domenic explained it better.