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

 

[Domenic]

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]

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.xlsm
	CE
3	HM9;AM17;HM30;AM31;AM50;HM60;AM75;AM86;HM96;HM104;AM110;HM126
4	AM31,10;AM50,31;HM60,10;AM75,31;AM86;HM96;HM104,87;AM110;HM126
5	HA60,58;AA75;AA86;HA96;HA104,76;AA110
6	HA9;AA17;HA30,86;AA31;AA50,87;HA60,80;AA75,59;AA86;HA96,18;HA104;AA110,81;HA126
7	HA9,37;AA17;HA30,4;AA31,22;AA50,22;HA60,32;AA86,61;HA96,72;HA126
8	HM9;AM17,17;HM30,86;AM31;AM50;HM60;AM75;AM86,83;HM104,3;AM110,80;HM126,79
9	HD9;AD17,46;AD75,44;AD86;HD96;HD104;AD110;HD126
10	HD9;AD17,40;HD30;AD31;AD50;HD80,80;AD75;AD86;HD96;HD104;AD110;HD126
11	HD9;AD17;HD30,86;AD31;AD50;HD60;AD75;AD86;HD96;HD104;AD110;HD126
12	HA9;AA17,60;HA30,71;AA31,68;AA50,68;HA60,67;AA75,70;HA96;HA104,87;AA110,52
13	HM9,78;AM17,44;HM30;AM31;AM50;HM60;AM75,59;AM86,7;HM104,14;AM110,10;HM126,29
14	AD17,44;HD30;AD31;AD50;HD60;AD75,46
15	AD17,44;HD30;AD31;AD50;HD60;AD75,40;HD96;HD104;AD110,60
16	AD75,50;AD86;AD110,30
17	HD9;AD17;AD60,9;HD126
18	HGK9;AGK17;HGK30;AGK31;AGK50;HGK60;AGK75;AGK86;HGK96;HGK104;AGK110;HGK126
19	HA30,44;AA75,31;AA86,29
20	HD30,4
21	HM9,53;AM17,46;HM30,19;AM50,3;HM60,23;AM75,20;HM96;HM104;AM110,38;HM126,11
22	AA17,30;HA30,46;AA31,80;AA50,59;HA104,3;AA110,9;HA126,61
23
24	HM9,12;AM17,3;HM30,4
Football

 

[Domenic]

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

 

[ToExcelOrNotToExcel]

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.

ok i get that but what should i do for my code to work correctly

 

[ToExcelOrNotToExcel]

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

I tried this an my code is not giving me the desired results, here is my new code

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
        
            If Len(cell.Value) > 0 Then
                
                Dim arr_1d() As String
                arr_1d = Split(cell.Value, ";")
                size_1d = UBound(arr_1d) - LBound(arr_1d)
                
                Range("CS20") = size_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_2d(1, 0)
                
            End If
            
        Next cell

End Sub

[\code]

 

[Domenic]

I don't know what you mean when you say that it's not giving you the desired result. Did my suggestion address your original error? If so, and if you have a new question, please start a new thread and ask your question there.

 

[ToExcelOrNotToExcel]

I don't know what you mean when you say that it's not giving you the desired result. Did my suggestion address your original error? If so, and if you have a new question, please start a new thread and ask your question there.

ok thank you