# 2d array redim problem



## ToExcelOrNotToExcel (Sunday at 8:40 AM)

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 (Sunday at 11:32 AM)

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 (Sunday at 3:38 PM)

6StringJazzer said:


> 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?
> 
> ...


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


----------



## johnnyL (Sunday at 6:45 PM)

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 (Monday at 12:19 PM)

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 (Monday at 12:49 PM)

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


----------



## johnnyL (Monday at 12:52 PM)

@Domenic you are spoiling the surprise.


----------



## Domenic (Monday at 12:53 PM)

johnnyL said:


> @Domenic you are spoiling the surprise.


----------



## 6StringJazzer (Monday at 2:59 PM)

Domenic said:


> 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 (Monday at 3:28 PM)

6StringJazzer said:


> 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.


----------



## ToExcelOrNotToExcel (Sunday at 8:40 AM)

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
```


----------



## Domenic (Monday at 3:31 PM)

When the argument for Split is a zero-length string ( "" ), it returns an empty array with no elements and no data.  In this case, UBound returns -1.  So let's say the cell is empty, we get the following...


```
size_1d = UBound(arr_1d) - LBound(arr_1d)

size_1d = ( -1 ) - ( 0 )

size_1d = -1
```

...which returns an error when ReDim'ing.


----------



## ToExcelOrNotToExcel (Yesterday at 9:36 AM)

6StringJazzer said:


> 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.


Hi I uploaded a mini-sheet with all the data from CE3: CE24

Betting forula 2022.xlsmCE3HM9;AM17;HM30;AM31;AM50;HM60;AM75;AM86;HM96;HM104;AM110;HM1264AM31,10;AM50,31;HM60,10;AM75,31;AM86;HM96;HM104,87;AM110;HM1265HA60,58;AA75;AA86;HA96;HA104,76;AA1106HA9;AA17;HA30,86;AA31;AA50,87;HA60,80;AA75,59;AA86;HA96,18;HA104;AA110,81;HA1267HA9,37;AA17;HA30,4;AA31,22;AA50,22;HA60,32;AA86,61;HA96,72;HA1268HM9;AM17,17;HM30,86;AM31;AM50;HM60;AM75;AM86,83;HM104,3;AM110,80;HM126,799HD9;AD17,46;AD75,44;AD86;HD96;HD104;AD110;HD12610HD9;AD17,40;HD30;AD31;AD50;HD80,80;AD75;AD86;HD96;HD104;AD110;HD12611HD9;AD17;HD30,86;AD31;AD50;HD60;AD75;AD86;HD96;HD104;AD110;HD12612HA9;AA17,60;HA30,71;AA31,68;AA50,68;HA60,67;AA75,70;HA96;HA104,87;AA110,5213HM9,78;AM17,44;HM30;AM31;AM50;HM60;AM75,59;AM86,7;HM104,14;AM110,10;HM126,2914AD17,44;HD30;AD31;AD50;HD60;AD75,4615AD17,44;HD30;AD31;AD50;HD60;AD75,40;HD96;HD104;AD110,6016AD75,50;AD86;AD110,3017HD9;AD17;AD60,9;HD12618HGK9;AGK17;HGK30;AGK31;AGK50;HGK60;AGK75;AGK86;HGK96;HGK104;AGK110;HGK12619HA30,44;AA75,31;AA86,2920HD30,421HM9,53;AM17,46;HM30,19;AM50,3;HM60,23;AM75,20;HM96;HM104;AM110,38;HM126,1122AA17,30;HA30,46;AA31,80;AA50,59;HA104,3;AA110,9;HA126,612324HM9,12;AM17,3;HM30,4Football


----------



## Domenic (Yesterday at 10:41 AM)

Did you check to see whether `size_1d` contains a negative value when the error occurs?

Does your range contain one or more empty cells?  If so, you should test for it in your loop, for example...


```
For Each cell In Rng
        If Len(cell.Value) > 0 Then
            'your code here
            '
            '
        End If
    Next cell
```


----------

