Extracting Comma Values from a String, Loading into Array

[MacroEcon1337]

I have a single column of values on an excel spreadsheet.

The contents of each individual cell correspond to Gate Codes, and are inputted exactly like the red text below:

example 1: 4
example 2: 4,6
example 3: 2,6,7
example 4: 5,12,1

Note: there will NEVER be a triple digit number - just 1-2 digit numbers (separated by commas).


The problem is I cannot figure out how to extract JUST the individual numbers (without the commas) -- and store them into an array. I thought I solved it, but then it failed on double digit numbers.


Not entirely relevant - but my end goal/purpose here is to subsequently loop through the array contents, then take certain actions based on their values. Any assistance greatly appreciated.

MC

 

[igold]

Very generically, this code would look at each example in your post and put each example into an array named "spl". This is assuming that the examples as posted start in cell A1 and continue down that column.

Sub test()


    Dim arr, spl
    Dim lRow As Long, i  As Long
    
    lRow = Cells(Rows.Count, 1).End(xlUp).Row
    arr = Range("A1:A" & lRow)
    For i = LBound(arr) To UBound(arr)
        spl = Split(arr(i, 1), ",")
    Next
    
End Sub

 

[MacroEcon1337]

Sub test()


    Dim arr, spl
    Dim lRow As Long, i  As Long
    
    lRow = Cells(Rows.Count, 1).End(xlUp).row
    arr = Range("A1:A" & lRow)
    
    For i = LBound(arr) To UBound(arr)
        spl = Split(arr(i, 1), ",")
       
    Next
   
   MsgBox spl(2)
   MsgBox arr(1)
     
End Sub

1. TY for the response. I tried the solution to no avail. Both of the MsgBox commands (see above - appended to the end of your code) result in "Subscript out of range" errors.

2. The key is that each individual cell has a "set" of multiple values (separated by commas). And I have to extract each set in

3. The pseudocode I see is:


Get current cell value (2,15,7)

Load myArray(1) = 2
Load myArray(2) = 15
Load myArray(3) = 7

At this point - I can look through myArray, perform some actions, clear myArray, move on to the next cell. Is there a way to modify the code to meet this goal? TY for the idea and help so far,

MC

 

[Rick Rothstein]

1. TY for the response. I tried the solution to no avail. Both of the MsgBox commands (see above - appended to the end of your code) result in "Subscript out of range" errors.

The lower bound for any array created by the Split function is always 0, so the upper bound for the array would always be the count of the number of elements in the array minus one.

 

[JoeMo]

Does this produce the array you want?

Sub test()


    Dim arr, sStr As String
    Dim lRow As Long, i  As Long
    
    lRow = Cells(Rows.Count, 1).End(xlUp).Row
    sStr = Join(Application.Transpose(Range("A1:A" & lRow)), ",")
    arr = Split(sStr, ",")
    For i = LBound(arr) To UBound(arr)
        MsgBox arr(i)
    Next i
     
End Sub

 

[MacroEcon1337]

Does this produce the array you want?

Sub test()


    Dim arr, sStr As String
    Dim lRow As Long, i  As Long
    
    lRow = Cells(Rows.Count, 1).End(xlUp).Row
    sStr = Join(Application.Transpose(Range("A1:A" & lRow)), ",")
    arr = Split(sStr, ",")
    For i = LBound(arr) To UBound(arr)
        MsgBox arr(i)
    Next i
     
End Sub

JoeMo - Worked like a charm, thank you!

Also Rick R - ty for the array bounding explanation.

 

[MacroEcon1337]

Does this produce the array you want?

Sub test()


    Dim arr, sStr As String
    Dim lRow As Long, i  As Long
    
    lRow = Cells(Rows.Count, 1).End(xlUp).Row
    sStr = Join(Application.Transpose(Range("A1:A" & lRow)), ",")
    arr = Split(sStr, ",")
    For i = LBound(arr) To UBound(arr)
        MsgBox arr(i)
    Next i
     
End Sub

Ah okay - one final thing, which I know I could eventually figure out on my own, so feel free to disregard me. Is there a way to tweak it, so that it ONLY returns the values for one cell (ie A1 or A2 or A3)? Otherwise, I have no way of knowing which values came from which cells (since they all message box at once).

My plan was to either clear, or erase and redim the array for each row. So we can pretend there is only one cell from which to extract the values -- then I will figure out how to work it into my loop.

EDIT: I DID try valiently by remove the "transpose" and hardcode "A1", but it did not work

 

[JoeMo]

Ah okay - one final thing, which I know I could eventually figure out on my own, so feel free to disregard me. Is there a way to tweak it, so that it ONLY returns the values for one cell (ie A1 or A2 or A3)? Otherwise, I have no way of knowing which values came from which cells (since they all message box at once).

My plan was to either clear, or erase and redim the array for each row. So we can pretend there is only one cell from which to extract the values -- then I will figure out how to work it into my loop.

EDIT: I DID try valiently by remove the "transpose" and hardcode "A1", but it did not work

I don't understand why you care which array element came from which cell. The transpose is an array whose individual elements contain the values for an individual cell - try the modified code below.

Sub test()


    Dim arr, sStr As String, arrByCell
    Dim lRow As Long, i  As Long
    
    lRow = Cells(Rows.Count, 1).End(xlUp).Row
    arrByCell = Application.Transpose(Range("A1:A" & lRow))
    sStr = Join(arrByCell, ",")
    arr = Split(sStr, ",")
    For i = LBound(arr) To UBound(arr)
        MsgBox arr(i)
    Next i
    'readout cell by cell sequentially
    For i = LBound(arrByCell) To UBound(arrByCell)
        MsgBox arrByCell(i)
    Next i
     
End Sub

 

[MacroEcon1337]

I don't understand why you care which array element came from which cell. The transpose is an array whose individual elements contain the values for an individual cell - try the modified code below.

Sub test()


    Dim arr, sStr As String, arrByCell
    Dim lRow As Long, i  As Long
    
    lRow = Cells(Rows.Count, 1).End(xlUp).Row
    arrByCell = Application.Transpose(Range("A1:A" & lRow))
    sStr = Join(arrByCell, ",")
    arr = Split(sStr, ",")
    For i = LBound(arr) To UBound(arr)
        MsgBox arr(i)
    Next i
    'readout cell by cell sequentially
    For i = LBound(arrByCell) To UBound(arrByCell)
        MsgBox arrByCell(i)
    Next i
     
End Sub

Nailed it - thank you!

 

[JoeMo]

Nailed it - thank you!

You are welcome - thanks for the reply.