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

ragont

New Member
Joined
Feb 2, 2014
Messages
5
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
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,200
Office Version
  1. 2016
Platform
  1. Windows
If you find - in array element, then call this function

VBA Code:
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
ABC
12-72,3,4,5,6,7
2
Sheet1
Cell Formulas
RangeFormula
C1C1=Span(A1)
 

ragont

New Member
Joined
Feb 2, 2014
Messages
5
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(n)
Loop
Span = Split(b)

End Function
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
Hi
Try
VBA Code:
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

Well-known Member
Joined
Nov 26, 2020
Messages
1,200
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

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

Watch MrExcel Video

Forum statistics

Threads
1,130,397
Messages
5,641,916
Members
417,246
Latest member
elbickel

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
Top