Array of numbers - from Application.InputBox

[Formula11]

I'm using this to create an array of numbers.
The example array is shown below.

Instead of entering {1,2,3,4,5,6,7,8,9,10,100,2000} ... wanted to input something like "1-10,100,2000".
Is this possible?

Sub arr_from_input()
    Dim arr_num As Variant
    Dim i As Long
    arr_num = Application.InputBox(prompt:="Enter values", Type:=64)
    For i = LBound(arr_num) To UBound(arr_num) - 0
        Debug.Print arr_num(i)
    Next i
End Sub

 

[CSmith]

I would suggest something like:

Sub arr_from_input()
    On Error GoTo Error_in_data
    Dim arr_num, arr_delim1 As Variant
    ReDim arr_delim2(0) As Integer
    Dim i As Long
    arr_num = Application.InputBox(prompt:="Enter values")
    arr_delim1 = Split(arr_num, ",")
    For i = LBound(arr_delim1) To UBound(arr_delim1) - 0
        currElements = UBound(arr_delim2) - LBound(arr_delim2)
        If InStr(arr_delim1(i), "-") Then
        iStart = Int(Val(Left(arr_delim1(i), InStr(arr_delim1(i), "-") - 1)))
        iEnd = Int(Val(Right(arr_delim1(i), Len(arr_delim1(i)) - InStr(arr_delim1(i), "-"))))
        currElements = UBound(arr_delim2) - LBound(arr_delim2) - 0
        ReDim Preserve arr_delim2(currElements + iEnd - iStart)
        For j = iStart To iEnd
        arr_delim2(currElements + j - iStart) = j
        Next j
        Else
        ReDim Preserve arr_delim2(currElements + 1)
        arr_delim2(currElements + 1) = Int(Val(arr_delim1(i)))
        End If
    Next i
GoTo End_Sub
Error_in_data:
Debug.Print "ERROR IN DATASET"
Stop
End_Sub:
    Debug.Print arr_num
    For i = LBound(arr_delim2) To UBound(arr_delim2) - 0
    Debug.Print arr_delim2(i)
    Next i
End Sub

 

[Formula11]

Thanks this does actually work for the above example given.
Tested for another set of numbers but misses by an element.
For "1-3,25,40,100-103,210-212", missing is 40 and 103.
Thanks, I'll continue based on this.

 

[CSmith]

Apologies missed the +1 on 2 lines

Sub arr_from_input()
    On Error GoTo Error_in_data
    Dim arr_num, arr_delim1 As Variant
    ReDim arr_delim2(0) As Integer
    Dim i As Long
    arr_num = Application.InputBox(prompt:="Enter values")
    arr_delim1 = Split(arr_num, ",")
    For i = LBound(arr_delim1) To UBound(arr_delim1) - 0
        currElements = UBound(arr_delim2) - LBound(arr_delim2)
        If InStr(arr_delim1(i), "-") Then
        iStart = Int(Val(Left(arr_delim1(i), InStr(arr_delim1(i), "-") - 1)))
        iEnd = Int(Val(Right(arr_delim1(i), Len(arr_delim1(i)) - InStr(arr_delim1(i), "-"))))
        currElements = UBound(arr_delim2) - LBound(arr_delim2) - 0
        ReDim Preserve arr_delim2(currElements + iEnd - iStart + 1)
        For j = iStart To iEnd
        arr_delim2(currElements + j - iStart + 1) = j
        Next j
        Else
        ReDim Preserve arr_delim2(currElements + 1)
        arr_delim2(currElements + 1) = Int(Val(arr_delim1(i)))
        End If
    Next i
GoTo End_Sub
Error_in_data:
Debug.Print "ERROR IN DATASET"
Stop
End_Sub:
    Debug.Print arr_num
    For i = LBound(arr_delim2) To UBound(arr_delim2) - 0
    Debug.Print arr_delim2(i)
    Next i
End Sub

 

[Georgiboy]

Maybe the below:

Sub test()
    Dim ib As String, var As Variant, x As Long, oVar As Variant
    
    ib = InputBox("Enter array", "Enter Values", "1-10,100,200")
    var = Split(ib, ",")
    For x = 0 To UBound(var)
        If InStr(var(x), "-") Then
            var(x) = Join(Application.Sequence(, Split(var(x), "-")(1), Split(var(x), "-")(0), 1), ",")
        End If
    Next x
    oVar = Join(var, ",")
    MsgBox oVar
End Sub

 

[CSmith]

Maybe the below:

Sub test()
    Dim ib As String, var As Variant, x As Long, oVar As Variant
   
    ib = InputBox("Enter array", "Enter Values", "1-10,100,200")
    var = Split(ib, ",")
    For x = 0 To UBound(var)
        If InStr(var(x), "-") Then
            var(x) = Join(Application.Sequence(, Split(var(x), "-")(1), Split(var(x), "-")(0), 1), ",")
        End If
    Next x
    oVar = Join(var, ",")
    MsgBox oVar
End Sub

Interesting approach does not work correctly for his above.

Where this does . . .

Sub arr_from_input()
    On Error GoTo Error_in_data
    Dim arr_num, arr_delim1 As Variant
    ReDim arr_delim2(0) As Integer
    Dim i As Long
    arr_num = Application.InputBox(prompt:="Enter values")
    arr_delim1 = Split(arr_num, ",")
    For i = LBound(arr_delim1) To UBound(arr_delim1) - 0
        currElements = UBound(arr_delim2) - LBound(arr_delim2)
        If InStr(arr_delim1(i), "-") Then
        iStart = Int(Val(Left(arr_delim1(i), InStr(arr_delim1(i), "-") - 1)))
        iEnd = Int(Val(Right(arr_delim1(i), Len(arr_delim1(i)) - InStr(arr_delim1(i), "-"))))
        currElements = UBound(arr_delim2) - LBound(arr_delim2) - 0
        ReDim Preserve arr_delim2(currElements + iEnd - iStart + 1)
        For j = iStart To iEnd
        arr_delim2(currElements + j - iStart + 1) = j
        Next j
        Else
        ReDim Preserve arr_delim2(currElements + 1)
        arr_delim2(currElements + 1) = Int(Val(arr_delim1(i)))
        End If
    Next i
GoTo End_Sub
Error_in_data:
Debug.Print "ERROR IN DATASET"
Stop
End_Sub:
    Debug.Print arr_num
    For i = LBound(arr_delim2) + 1 To UBound(arr_delim2) - 0
    Debug.Print arr_delim2(i)
    Next i
End Sub

 

[Georgiboy]

Thanks for pointing that out @CSmith, amended code below:

Sub test()
    Dim ib As String, var As Variant, x As Long, oVar As Variant
   
    ib = InputBox("Enter array", "Enter Values", "1-3,25,40,100-103,210-212")
    var = Split(ib, ",")
    For x = 0 To UBound(var)
        If InStr(var(x), "-") Then
            var(x) = Join(Application.Sequence(, Split(var(x), "-")(1) - Split(var(x), "-")(0) + 1, Split(var(x), "-")(0), 1), ",")
        End If
    Next x
    oVar = Join(var, ",")
    MsgBox oVar
End Sub

The mistake was not to take the Min part of '100-103' away from the Max part of '100-103' meaning it was creating too many columns of numbers.

 

[Formula11]

Great, thanks to both for the input.

 

[CSmith]

Great, thanks to both for the input.

Welcome! Thanks for the feedback!