Turning a 1d array into a 2d array

[ToExcelOrNotToExcel]

Hello everyone i have a string that looks like this "HM9;AM17;HM30, 12;HM40,55" . I'm trying to turn it into a 2d array which where I first split it using ";" and then using "," for the second dimension. Assuming i willl create a 2d array called arr_2d(), the positions of the elements will be as follows: arr_2d(0) = HM9, arr-2d(1) = AM17, arr_2d(2,1) = HM30, arr_2d(2,2) = 12.... The code i have is not returning the correct values i will post it below.

Sub Module_1()

Dim input_data As String
input_data = "HM9;AM17;HM30, 12;HM40,55" 'input text

Dim arr_1d() As String
arr_1d = Split(input_data, ";") 'setup 1D array

Dim size_1d As Integer
size_1d = UBound(arr_1d) - LBound(arr_1d) 'how many elements in array

Dim arr_2d() As String
ReDim arr_2d(size_1d, 1) As String 'setup for 2d array, needs to be redimmed because VBA sucks

Dim i As Long 'index for the loop
For i = 0 To size_1d 'loop through each item in the 1D array
Dim temp() As String
temp = Split(arr_1d(i), ",") ' split the elements by comma

Dim size_temp As Integer
size_temp = UBound(temp) - LBound(temp) + 1 'how many elements in temp array

'the "trim" is to remove any spaces, get rid of it if you want them
If size_temp = 1 Then 'only one element
arr_2d(i, 0) = Trim(temp(0))
ElseIf size_temp = 2 Then '2 elements
arr_2d(i, 0) = Trim(temp(0))
arr_2d(i, 1) = Trim(temp(1))
Else
'this shouldn't get hit but may if you have more than 1 comma in your original data.
'consider adding a msgbox or something just in case
End If
Next i

MsgBox ("Done!")

End Sub

 

[Peter_SSs]

Welcome to the MrExcel board!

The code i have is not returning the correct values

Your code returns these values for me. Which ones do you consider wrong and what should those 'wrong' values actually be?

BTW, When posting vba code in the forum, please indent your code and use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. For example, your code would be much easier to deal with like this

Sub Module_1()

  Dim input_data As String
  input_data = "HM9;AM17;HM30, 12;HM40,55" 'input text
  
  Dim arr_1d() As String
  arr_1d = Split(input_data, ";") 'setup 1D array
  
  Dim size_1d As Integer
  size_1d = UBound(arr_1d) - LBound(arr_1d) 'how many elements in array
  
  Dim arr_2d() As String
  ReDim arr_2d(size_1d, 1) As String 'setup for 2d array, needs to be redimmed because VBA sucks
  
  Dim i As Long 'index for the loop
  For i = 0 To size_1d 'loop through each item in the 1D array
    Dim temp() As String
    temp = Split(arr_1d(i), ",") ' split the elements by comma
    
    Dim size_temp As Integer
    size_temp = UBound(temp) - LBound(temp) + 1 'how many elements in temp array
    
    'the "trim" is to remove any spaces, get rid of it if you want them
    If size_temp = 1 Then 'only one element
      arr_2d(i, 0) = Trim(temp(0))
    ElseIf size_temp = 2 Then '2 elements
      arr_2d(i, 0) = Trim(temp(0))
      arr_2d(i, 1) = Trim(temp(1))
    Else
      'this shouldn't get hit but may if you have more than 1 comma in your original data.
      'consider adding a msgbox or something just in case
    End If
  Next i
  
  MsgBox ("Done!")
End Sub

 

[ToExcelOrNotToExcel]

Welcome to the MrExcel board!


Your code returns these values for me. Which ones do you consider wrong and what should those 'wrong' values actually be?

View attachment 82267

BTW, When posting vba code in the forum, please indent your code and use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. For example, your code would be much easier to deal with like this

Sub Module_1()

  Dim input_data As String
  input_data = "HM9;AM17;HM30, 12;HM40,55" 'input text
 
  Dim arr_1d() As String
  arr_1d = Split(input_data, ";") 'setup 1D array
 
  Dim size_1d As Integer
  size_1d = UBound(arr_1d) - LBound(arr_1d) 'how many elements in array
 
  Dim arr_2d() As String
  ReDim arr_2d(size_1d, 1) As String 'setup for 2d array, needs to be redimmed because VBA sucks
 
  Dim i As Long 'index for the loop
  For i = 0 To size_1d 'loop through each item in the 1D array
    Dim temp() As String
    temp = Split(arr_1d(i), ",") ' split the elements by comma
   
    Dim size_temp As Integer
    size_temp = UBound(temp) - LBound(temp) + 1 'how many elements in temp array
   
    'the "trim" is to remove any spaces, get rid of it if you want them
    If size_temp = 1 Then 'only one element
      arr_2d(i, 0) = Trim(temp(0))
    ElseIf size_temp = 2 Then '2 elements
      arr_2d(i, 0) = Trim(temp(0))
      arr_2d(i, 1) = Trim(temp(1))
    Else
      'this shouldn't get hit but may if you have more than 1 comma in your original data.
      'consider adding a msgbox or something just in case
    End If
  Next i
 
  MsgBox ("Done!")
End Sub

I will remember to indent, let me check it again

 

[ToExcelOrNotToExcel]

Welcome to the MrExcel board!


Your code returns these values for me. Which ones do you consider wrong and what should those 'wrong' values actually be?

View attachment 82267

BTW, When posting vba code in the forum, please indent your code and use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. For example, your code would be much easier to deal with like this

Sub Module_1()

  Dim input_data As String
  input_data = "HM9;AM17;HM30, 12;HM40,55" 'input text
 
  Dim arr_1d() As String
  arr_1d = Split(input_data, ";") 'setup 1D array
 
  Dim size_1d As Integer
  size_1d = UBound(arr_1d) - LBound(arr_1d) 'how many elements in array
 
  Dim arr_2d() As String
  ReDim arr_2d(size_1d, 1) As String 'setup for 2d array, needs to be redimmed because VBA sucks
 
  Dim i As Long 'index for the loop
  For i = 0 To size_1d 'loop through each item in the 1D array
    Dim temp() As String
    temp = Split(arr_1d(i), ",") ' split the elements by comma
   
    Dim size_temp As Integer
    size_temp = UBound(temp) - LBound(temp) + 1 'how many elements in temp array
   
    'the "trim" is to remove any spaces, get rid of it if you want them
    If size_temp = 1 Then 'only one element
      arr_2d(i, 0) = Trim(temp(0))
    ElseIf size_temp = 2 Then '2 elements
      arr_2d(i, 0) = Trim(temp(0))
      arr_2d(i, 1) = Trim(temp(1))
    Else
      'this shouldn't get hit but may if you have more than 1 comma in your original data.
      'consider adding a msgbox or something just in case
    End If
  Next i
 
  MsgBox ("Done!")
End Sub

thank you for the help

 

[Peter_SSs]

You're welcome.