Collect an array of numbers and enter them:

This Procedure will display an Input box that asks the user to enter numbers below 100 seperated by a commas and/or a dash. It then enters the numbers into a range (Column A in this case).

Sub ParseNumbers()

Dim sIn As String

Dim iCount As Integer



 'Trim removes excess spaces(not single spaces between text)

 sIn = InputBox("Enter Numbers below 100 seperated by" _

  & "a comma or dash eg 1,14-16,2,22", "OzGrid Business Applications")



 If sIn = "" Then Exit Sub 'They canceled

   With WorksheetFunction

   'Remove the comma.

   sIn = Trim(.Substitute(sIn, ",", " "))

   'Remove the dash.

   sIn = Trim(.Substitute(sIn, "-", " "))

   'Count the characters after removing spaces.

   iCount = Len(.Substitute(sIn, " ", ""))

   'Loop as many times as there are characters

    For i = 1 To iCount

     'Extract first 2 numbers, Trim will remove _

     trailing or leading spaces.

     Cells(i, 1) = Trim(Mid(sIn, 1, 2))

     'Replace the number extracted with empty text

     sIn = Trim(.Substitute(sIn, Cells(i, 1), " ", 1))

      If sIn = "" Then Exit For 'No more numbers

   Next i

  End With

End Sub