Extract all numbers in an expression

[ChewCS]

Hi, I would like to seek assistance from this forum in a certain task.

I have a cell with an expression that includes ranges of numbers. Something like "1-3,13,15-17". I would like to have a VBA function that extracts all the numbers within this single expression and store them in an array. Something like

array(1,1) is 1, array(1,2) is 2, array(1,3) is 3, array(1,4) is 13, array(1,5) is 15, array(1,6) is 16, array(1,7) is 17.

Is it possible?

Thanks in advance!

 

[ChewCS]

Change this line

arg = "'" & path & "[" & file & "]" & sheet & "'!" & ref

as

arg = "'" & path & "[" & file & "]" & sheet & "'!" & ref.address

Hi, I am getting a Run-time error "9", subscript out of range , and the highlighted line is

ary(Ro, Val(M(T))) = 1

 

[kvsrinivasamurthy]

Is the value is more than 18.

 

[Alex Blakenburg]

Is the value is more than 18.

I think you will find that Val(M(T)) = 0 which out of range to ary.

The 3 changes I made to @kvsrinivasamurthy's code to get it to work for me were:
Added address per K

arg = "'" & path & "[" & file & "]" & sheet & "'!" & ref.Address

Replaced FormulaArray with just Formula

.Formula = "=" & arg

Added an If statement to ary(Ro, Val(M(T))) = 1.
Ideally we would exit both loops at this point but start with this.
Rationale: The formulas pulling in the data from the closed file return a Zero value when it runs out of data rows in the import data source.
The macro is using the value returned as weeks (1-18). ary is dimensioned to start at 1, the zero is out of range and in any case signifies the end of the data.

        Else
            If M(T) <> 0 Then ary(Ro, Val(M(T))) = 1    ' ADDED IF STATEMENT
        End If
  
    Next T

 

[kvsrinivasamurthy]

Change the for loop code as below.

For Ro = 1 To UBound(S, 1)
  If S(Ro, 1) <> "" Then
    P = Split(Replace(S(Ro, 1), "-", ","), ",")
    M = Split(S(Ro, 1), ",")
    For T = 0 To UBound(M)
  
        If InStr(1, M(T), "-") > 0 Then
            N = Split(M(T), "-")
            For Ta = Val(N(0)) To Val(N(UBound(N)))
            ary(Ro, Ta) = 1
            Next Ta
        Else
            ary(Ro, Val(M(T))) = 1
        End If
    Next T
  End If
Next Ro

 

[ChewCS]

Hi gurus, I've managed to run both your solutions! Thank you so much! As I can only provide one "Mark as solution", I hesitate to mark any. But I will edit my original post to indicate both are working. Once again, thank you so much for your kind assistance!!

 

[ChewCS]

Hi, I would like to seek assistance from this forum in a certain task.

I have a cell with an expression that includes ranges of numbers. Something like "1-3,13,15-17". I would like to have a VBA function that extracts all the numbers within this single expression and store them in an array. Something like

array(1,1) is 1, array(1,2) is 2, array(1,3) is 3, array(1,4) is 13, array(1,5) is 15, array(1,6) is 16, array(1,7) is 17.

Is it possible?

Thanks in advance!

Looks like I can't edit my posts.

Post #34 by @kvsrinivasamurthy works

And Post #22 by @Alex Blakenburg works too.

 

[Alex Blakenburg]

Thank you for testing and providing feedback on both options. The post references were a value add . Glad we were able to help.