Split text in one array from another array created from textbox input

[ragont]

If been trying a few different ways to extract numbers from a textbox. Firstly I have successfully cerated an array by delimiting the comma from the textbox data, now i want to search through that array for a "-" to Extract the numbers on either side. finally, which I haven't even got to yet, i then want to repopulate the array with all the number in between the two values.

Eg. Textbox entry = 1,2-4,5-9,10
First array successfully results in
1
2-4
5-9
10

I now want to search those values and extract
2
4
5
9

Finally i want to redim (i think) the ary2 to have all whole numbers between the values EG. 2,3,4 and 5,6,7,8,9

Here's my code including some comment codes that I may not need but was keeping as a reference for trial an error

Private Sub CellNumbers_AfterUpdate()
Dim i, i2, counti As Long
Dim number As Variant
Dim ary() As String
Dim ary2() As String
ary = Split(CStr(CellNumbers.Text), ",")


For i = LBound(ary()) To UBound(ary())
If InStr(1, i, "-", vbTextCompare) Then
ary2 = Split(CStr(ary(i)), "-")
'For i2 = LBound(ary2()) To UBound(ary2())
'ary2 = LBound(ary2()) + 1
'counti = i + 1
'Next
End If
Next

For Each number In ary()
Debug.Print number
Next

For Each number In ary2()
Debug.Print number
Next
'newcount = UBound(ary) + 1 + counti

End Sub

 

[Zot]

If you find - in array element, then call this function

Function Span(str As String)

Dim a$()
Dim n As Long
Dim b As String

a = Split(str, "-")
b = a(0)
n = b
Do While n < a(UBound(a))
    n = n + 1
    b = b & "," & CStr(n)
Loop
Span = Split(b)

End Function

Book3
	A	B	C
1	2-7		2,3,4,5,6,7
2
Sheet1

Cell Formulas
Range	Formula
C1	C1	=Span(A1)

 

[ragont]

Thanks Zot. I can't seem to workout how to call the function properly. not getting any output

Private Sub CellNumbers_AfterUpdate()
Dim i, i2, counti As Long
Dim number As Variant
Dim ary() As String
Dim ary2() As String

ary = Split(CStr(CellNumbers.Text), ",")

For i = LBound(ary()) To UBound(ary())
If InStr(1, i, "-") > 0 Then
ary2 = Span(ary(i))
End If
On Error Resume Next
Next

For Each number In ary2()
Debug.Print number
Next

End Sub

Function Span(str As String)

Dim a$()
Dim n As Long
Dim b As String

a = Split(str, "-")
b = a(o)
n = b
Do While n < a(UBound(a))
n = n + 1
b = b & "," & CStr
Loop
Span = Split(b)

End Function

 

[mohadin]

Hi
Try

Private Sub CellNumbers_AfterUpdate()
    Dim i, i2, counti As Long
    Dim number As Variant
    Dim ary() As String
    Dim ary2() As String
    Dim X, K
    ary = Split(CStr(CellNumbers.Text), ",")
    ReDim ary2(1 To 100)
    K = 1
    For i = LBound(ary()) To UBound(ary())
        If InStr(1, ary(i), "-", vbTextCompare) Then
            X = Split(CStr(ary(i)), "-")
            ary2(K) = X(0): ary2(K + 1) = X(1)
            
            Debug.Print ary2(K)
            Debug.Print ary2(K + 1)
            K = K + 2
        End If
    Next
    ReDim Preserve ary2(1 To K - 1)
End Sub

 

[Zot]

There is a mistake in my function. Note that I hard coded sample for testing
ary = Split("1,2-4,5-9,10", ",")

The Span = Split(b) just created a single element array because I did not put comma. This works

Private Sub CellNumbers_AfterUpdate()
Dim i, i2, counti As Long
Dim number As Variant
Dim ary() As String
Dim ary2() As String
Dim aryResult() As String

ReDim aryResult(1)
ary = Split("1,2-4,5-9,10", ",")

For i = LBound(ary()) To UBound(ary())
    If InStr(ary(i), "-") > 0 Then
        ary2 = Span(ary(i))
        ReDim Preserve aryResult(UBound(aryResult) + UBound(ary2) + 1)
        For Each number In ary2
            aryResult(i2) = number
            i2 = i2 + 1
        Next
    Else
        aryResult(i2) = ary(i)
        i2 = i2 + 1
    End If
Next

For Each number In aryResult
    Debug.Print number
Next

End Sub

Function Span(str As String)

Dim a$()
Dim n As Long
Dim b As String

a = Split(str, "-")
b = a(o)
n = b
Do While n < a(UBound(a))
    n = n + 1
    b = b & "," & CStr(n)
Loop
Span = Split(b, ",")

End Function

 

[ragont]

Thanks Zot, works a charm

 

[Zot]

Thanks Zot, works a charm

Thanks for update. Note that
Dim i, i2, counti As Long

will define i and i2 as Variant. Only counti is Long