Solved: VBA: Extract Number from Cell

[taporctv]

Did a search but didn't get the exact results i was looking for.

Here's my situation, i have a cell with multiple numbers in it. The numbers are separated by a comma, and sometimes there is a number range in the cell. So for example, one cell may contain 39,40,43,45-49. How do I extract the numbers? Also for 45-49, I want all the numbers in between i.e. 45 46 47 48 49.

Any help is appreciated.

[mdmackillop]

How do you want the result returned?

[Bob Phillips]

How do you want the result returned?

I was just about to post the same question :)

[taporctv]

I want to store the results in an array.

[mdmackillop]

Option Explicit
Sub Test()
Dim rng As Range, Num
Dim Result(), Arr, x, r
Dim i As Long, j As Long, k As Long
Dim msg As String



ReDim Result(100)
Set rng = Range("d5")
Arr = Split(rng, ",")
For i = 0 To UBound(Arr)
Num = Arr(i)
If InStr(1, Num, "-") = 0 Then
Result(j) = Num
j = j + 1
Else
x = Split(Num, "-")
For k = x(0) To x(1)
Result(j) = k
j = j + 1
Next
End If
Next
ReDim Preserve Result(j - 1)

For Each r In Result
msg = msg & r & vbCr
Next
MsgBox msg
End Sub

[taporctv]

That worked! Thanks!