2d array redim problem

ToExcelOrNotToExcel

New Member
Joined
Jan 7, 2023
Messages
13
Office Version
  1. 2019
Platform
  1. Windows
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.

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

VBA Code:
size_1d = UBound(arr_1d) - LBound(arr_1d)

size_1d = ( -1 ) - ( 0 )

size_1d = -1

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

Excel Facts

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

VBA Code:
    For Each cell In Rng
        If Len(cell.Value) > 0 Then
            'your code here
            '
            '
        End If
    Next cell
 
Upvote 0
Solution
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...

VBA Code:
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
 
Upvote 0
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...

VBA Code:
    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

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]
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,569
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top