Array not returning expected value

[ToExcelOrNotToExcel]

Hello everyone, I'm trying to loop through a range of cells and have and store them in a 2d array. I'll post my data below but firstly I'm I want it to separate everything by ";" and then for the second dimension by ",". I want to perform some calculations but they work this code doesn't. I don't know if i should destroy the array after every loop or how to do that. anyway here is the data below.

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

Here is the code below. I will use the last cell CE24 as an example of what I want. arr_2d(0,0) = HM9, arr_2d(0,1) = 2, arr_2d(1,0) = AM17, arr_2d(1,1) = 3

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
                            
            End If
            
        Next cell

End Sub

[\code]

 

[DanteAmor]

HM9,12;AM17,3;HM30,4

You mean:

I will use the last cell CE24 as an example of what I want. arr_2d(0,0) = HM9, arr_2d(0,1) = 12, arr_2d(1,0) = AM17, arr_2d(1,1) = 3

I'm not sure what you're going to do with the data in the array, but with the following code, the second array contains one row for each ";" separator, and in that row in column 1 the first data and in column 2 the second data if it has a comma as a separator.
The result of puts it in the CG and CH columns.

Sub FillPlayerRating()
  Dim cell As Range
  Dim i As Long, n As Long
  Dim p As Variant, arr_2d As Variant
  
  With Range("CE3:CE24")
    n = Evaluate("=SUM(LEN(" & .Address & ")-LEN(SUBSTITUTE(" & .Address & ","";"",""""))+1)")
    ReDim arr_2d(1 To n, 1 To 2)
  
    For Each cell In .Cells
      For Each p In Split(cell, ";")
        i = i + 1
        arr_2d(i, 1) = Trim(Split(p, ",")(0))
        If InStr(1, p, ",") > 0 Then arr_2d(i, 2) = Trim(Split(p, ",")(1))
      Next
    Next
  End With
  Range("CG3").Resize(i, 2).Value = arr_2d
End Sub

Example:

Dante Amor
	CE	CF	CG	CH
1
2
3	HM9;AM17;HM30;AM31;AM50;HM60;AM75;AM86;HM96;HM104;AM110;HM126		HM9
4	AM31,10;AM50,31;HM60,10;AM75,31;AM86;HM96;HM104,87;AM110;HM126		AM17
5	HA60,58;AA75;AA86;HA96;HA104,76;AA110		HM30
6	HA9;AA17;HA30,86;AA31;AA50,87;HA60,80;AA75,59;AA86;HA96,18;HA104;AA110,81;HA126		AM31
7	HA9,37;AA17;HA30,4;AA31,22;AA50,22;HA60,32;AA86,61;HA96,72;HA126		AM50
8	HM9;AM17,17;HM30,86;AM31;AM50;HM60;AM75;AM86,83;HM104,3;AM110,80;HM126,79		HM60
9	HD9;AD17,46;AD75,44;AD86;HD96;HD104;AD110;HD126		AM75
10	HD9;AD17,40;HD30;AD31;AD50;HD80,80;AD75;AD86;HD96;HD104;AD110;HD126		AM86
11	HD9;AD17;HD30,86;AD31;AD50;HD60;AD75;AD86;HD96;HD104;AD110;HD126		HM96
12	HA9;AA17,60;HA30,71;AA31,68;AA50,68;HA60,67;AA75,70;HA96;HA104,87;AA110,52		HM104
13	HM9,78;AM17,44;HM30;AM31;AM50;HM60;AM75,59;AM86,7;HM104,14;AM110,10;HM126,29		AM110
14	AD17,44;HD30;AD31;AD50;HD60;AD75,46		HM126
15	AD17,44;HD30;AD31;AD50;HD60;AD75,40;HD96;HD104;AD110,60		AM31	10
16	AD75,50;AD86;AD110,30		AM50	31
17	HD9;AD17;AD60,9;HD126		HM60	10
18	HGK9;AGK17;HGK30;AGK31;AGK50;HGK60;AGK75;AGK86;HGK96;HGK104;AGK110;HGK126		AM75	31
19	HA30,44;AA75,31;AA86,29		AM86
20	HD30,4		HM96
21	HM9,53;AM17,46;HM30,19;AM50,3;HM60,23;AM75,20;HM96;HM104;AM110,38;HM126,11		HM104	87
22	AA17,30;HA30,46;AA31,80;AA50,59;HA104,3;AA110,9;HA126,61		AM110
23			HM126
24	HM9,12;AM17,3;HM30,4		HA60	58
25			AA75
26			AA86
27			HA96
28			HA104	76
29			AA110
30			HA9
31			AA17
32			HA30	86
33			AA31
34			AA50	87
35			HA60	80
36			AA75	59
37			AA86
38			HA96	18
39			HA104
40			AA110	81
41			HA126
42			HA9	37
43			AA17
44			HA30	4
45			AA31	22
46			AA50	22
47			HA60	32
48			AA86	61
49			HA96	72
50			HA126
51			HM9
52			AM17	17
53			HM30	86
54			AM31
55			AM50
56			HM60
57			AM75
58			AM86	83
59			HM104	3
60			AM110	80
61			HM126	79
62			HD9
63			AD17	46
64			AD75	44
65			AD86
66			HD96
67			HD104
68			AD110
69			HD126
70			HD9
71			AD17	40
72			HD30
73			AD31
74			AD50
75			HD80	80
76			AD75
77			AD86
78			HD96
79			HD104
80			AD110
81			HD126
82			HD9
83			AD17
84			HD30	86
85			AD31
86			AD50
87			HD60
88			AD75
89			AD86
90			HD96
91			HD104
92			AD110
93			HD126
94			HA9
95			AA17	60
96			HA30	71
97			AA31	68
98			AA50	68
99			HA60	67
100			AA75	70
101			HA96
102			HA104	87
103			AA110	52
104			HM9	78
105			AM17	44
106			HM30
107			AM31
108			AM50
109			HM60
110			AM75	59
111			AM86	7
112			HM104	14
113			AM110	10
114			HM126	29
115			AD17	44
116			HD30
117			AD31
118			AD50
119			HD60
120			AD75	46
121			AD17	44
122			HD30
123			AD31
124			AD50
125			HD60
126			AD75	40
127			HD96
128			HD104
129			AD110	60
130			AD75	50
131			AD86
132			AD110	30
133			HD9
134			AD17
135			AD60	9
136			HD126
137			HGK9
138			AGK17
139			HGK30
140			AGK31
141			AGK50
142			HGK60
143			AGK75
144			AGK86
145			HGK96
146			HGK104
147			AGK110
148			HGK126
149			HA30	44
150			AA75	31
151			AA86	29
152			HD30	4
153			HM9	53
154			AM17	46
155			HM30	19
156			AM50	3
157			HM60	23
158			AM75	20
159			HM96
160			HM104
161			AM110	38
162			HM126	11
163			AA17	30
164			HA30	46
165			AA31	80
166			AA50	59
167			HA104	3
168			AA110	9
169			HA126	61
170			HM9	12
171			AM17	3
172			HM30	4
Hoja6

 

[DanteAmor]

The following code does the same thing as the previous one.

Sub test3()
  Dim c
  c = Split(Join(Application.Transpose(Range("CE3:CE24").Value), ";"), ";")
  With Range("CG3").Resize(UBound(c) + 1)
    .Value = Application.Transpose(c)
    .TextToColumns .Cells(1), xlDelimited, Comma:=True
  End With
End Sub

 

[ToExcelOrNotToExcel]

The following code does the same thing as the previous one.

Sub test3()
  Dim c
  c = Split(Join(Application.Transpose(Range("CE3:CE24").Value), ";"), ";")
  With Range("CG3").Resize(UBound(c) + 1)
    .Value = Application.Transpose(c)
    .TextToColumns .Cells(1), xlDelimited, Comma:=True
  End With
End Sub

hi, you used the ";" twice shouldn't the second one be a comma "," and is c is a 2d array?

 

[ToExcelOrNotToExcel]

You mean:


I'm not sure what you're going to do with the data in the array, but with the following code, the second array contains one row for each ";" separator, and in that row in column 1 the first data and in column 2 the second data if it has a comma as a separator.
The result of puts it in the CG and CH columns.

Sub FillPlayerRating()
  Dim cell As Range
  Dim i As Long, n As Long
  Dim p As Variant, arr_2d As Variant
 
  With Range("CE3:CE24")
    n = Evaluate("=SUM(LEN(" & .Address & ")-LEN(SUBSTITUTE(" & .Address & ","";"",""""))+1)")
    ReDim arr_2d(1 To n, 1 To 2)
 
    For Each cell In .Cells
      For Each p In Split(cell, ";")
        i = i + 1
        arr_2d(i, 1) = Trim(Split(p, ",")(0))
        If InStr(1, p, ",") > 0 Then arr_2d(i, 2) = Trim(Split(p, ",")(1))
      Next
    Next
  End With
  Range("CG3").Resize(i, 2).Value = arr_2d
End Sub

Example:

Dante Amor
	CE	CF	CG	CH
1
2
3	HM9;AM17;HM30;AM31;AM50;HM60;AM75;AM86;HM96;HM104;AM110;HM126		HM9
4	AM31,10;AM50,31;HM60,10;AM75,31;AM86;HM96;HM104,87;AM110;HM126		AM17
5	HA60,58;AA75;AA86;HA96;HA104,76;AA110		HM30
6	HA9;AA17;HA30,86;AA31;AA50,87;HA60,80;AA75,59;AA86;HA96,18;HA104;AA110,81;HA126		AM31
7	HA9,37;AA17;HA30,4;AA31,22;AA50,22;HA60,32;AA86,61;HA96,72;HA126		AM50
8	HM9;AM17,17;HM30,86;AM31;AM50;HM60;AM75;AM86,83;HM104,3;AM110,80;HM126,79		HM60
9	HD9;AD17,46;AD75,44;AD86;HD96;HD104;AD110;HD126		AM75
10	HD9;AD17,40;HD30;AD31;AD50;HD80,80;AD75;AD86;HD96;HD104;AD110;HD126		AM86
11	HD9;AD17;HD30,86;AD31;AD50;HD60;AD75;AD86;HD96;HD104;AD110;HD126		HM96
12	HA9;AA17,60;HA30,71;AA31,68;AA50,68;HA60,67;AA75,70;HA96;HA104,87;AA110,52		HM104
13	HM9,78;AM17,44;HM30;AM31;AM50;HM60;AM75,59;AM86,7;HM104,14;AM110,10;HM126,29		AM110
14	AD17,44;HD30;AD31;AD50;HD60;AD75,46		HM126
15	AD17,44;HD30;AD31;AD50;HD60;AD75,40;HD96;HD104;AD110,60		AM31	10
16	AD75,50;AD86;AD110,30		AM50	31
17	HD9;AD17;AD60,9;HD126		HM60	10
18	HGK9;AGK17;HGK30;AGK31;AGK50;HGK60;AGK75;AGK86;HGK96;HGK104;AGK110;HGK126		AM75	31
19	HA30,44;AA75,31;AA86,29		AM86
20	HD30,4		HM96
21	HM9,53;AM17,46;HM30,19;AM50,3;HM60,23;AM75,20;HM96;HM104;AM110,38;HM126,11		HM104	87
22	AA17,30;HA30,46;AA31,80;AA50,59;HA104,3;AA110,9;HA126,61		AM110
23			HM126
24	HM9,12;AM17,3;HM30,4		HA60	58
25			AA75
26			AA86
27			HA96
28			HA104	76
29			AA110
30			HA9
31			AA17
32			HA30	86
33			AA31
34			AA50	87
35			HA60	80
36			AA75	59
37			AA86
38			HA96	18
39			HA104
40			AA110	81
41			HA126
42			HA9	37
43			AA17
44			HA30	4
45			AA31	22
46			AA50	22
47			HA60	32
48			AA86	61
49			HA96	72
50			HA126
51			HM9
52			AM17	17
53			HM30	86
54			AM31
55			AM50
56			HM60
57			AM75
58			AM86	83
59			HM104	3
60			AM110	80
61			HM126	79
62			HD9
63			AD17	46
64			AD75	44
65			AD86
66			HD96
67			HD104
68			AD110
69			HD126
70			HD9
71			AD17	40
72			HD30
73			AD31
74			AD50
75			HD80	80
76			AD75
77			AD86
78			HD96
79			HD104
80			AD110
81			HD126
82			HD9
83			AD17
84			HD30	86
85			AD31
86			AD50
87			HD60
88			AD75
89			AD86
90			HD96
91			HD104
92			AD110
93			HD126
94			HA9
95			AA17	60
96			HA30	71
97			AA31	68
98			AA50	68
99			HA60	67
100			AA75	70
101			HA96
102			HA104	87
103			AA110	52
104			HM9	78
105			AM17	44
106			HM30
107			AM31
108			AM50
109			HM60
110			AM75	59
111			AM86	7
112			HM104	14
113			AM110	10
114			HM126	29
115			AD17	44
116			HD30
117			AD31
118			AD50
119			HD60
120			AD75	46
121			AD17	44
122			HD30
123			AD31
124			AD50
125			HD60
126			AD75	40
127			HD96
128			HD104
129			AD110	60
130			AD75	50
131			AD86
132			AD110	30
133			HD9
134			AD17
135			AD60	9
136			HD126
137			HGK9
138			AGK17
139			HGK30
140			AGK31
141			AGK50
142			HGK60
143			AGK75
144			AGK86
145			HGK96
146			HGK104
147			AGK110
148			HGK126
149			HA30	44
150			AA75	31
151			AA86	29
152			HD30	4
153			HM9	53
154			AM17	46
155			HM30	19
156			AM50	3
157			HM60	23
158			AM75	20
159			HM96
160			HM104
161			AM110	38
162			HM126	11
163			AA17	30
164			HA30	46
165			AA31	80
166			AA50	59
167			HA104	3
168			AA110	9
169			HA126	61
170			HM9	12
171			AM17	3
172			HM30	4
Hoja6

hi what does "CG3" have to do with anything