You can only "preserve" the last dimension of an array.
I recommend you see if Ron Rosenfeld's regular expressions code
does what you want before going further down the looping path.
--
Jim Cone
Portland, Oregon USA




wrote in message
Jim, your code is very helpful. I have modified it slightly to add a second element to the array to
capture the character position in the strArr.

I am not sure how to correctly ReDim Preserve the new 2 element strArr. Currently, I get a
"Subscript out of range" the way it is. I tried ReDim Preserve(1 To x).elements(1 to 2) but it
failed.

Sub FigureItOut()
Dim N As Long
Dim M As Long
Dim x As Long
Dim strWhat As String
Dim strGiven As String
Dim vThings As Variant
Dim strArr() As String

ReDim strArr(1 To 100, 1 To 2) 'Note the 2nd element

'some extras in the string
strGiven = "-9'Min. Int.'!F26-'Min.-7 Int.'!F31+28038^35+[C:\123]'Clos-6ing'!E3^1"
vThings = Array("-", "+", "^", "\", "/", "*")

M = 0
For N = 0 To UBound(vThings)
Do
M = InStr(M + 1, strGiven, vThings(N), vbBinaryCompare)
If M 0 Then
If Mid$(strGiven, M + 1, 1) Like "#" Then
strWhat = Mid$(strGiven, M, 2)
x = x + 1
strArr(x, 1) = strWhat
strArr(x, 2) = M + 1 'Note the 2nd element
End If
Else
Exit Do
End If
Loop
Next

ReDim Preserve strArr(1 To x) 'This line fails because I added a 2nd element
For x = 0 To UBound(strArr)
Debug.Print strArr(x)
Next
End Sub

Except for the "\1", which I do not want, your code gives me the "Operator" and the first digit and
now its position, in the strGiven. As each TestChar approaches the Case below I could test if
TestChar IsNumeric(strArr(x)) AND if it is within the position-range of the entire Numeric.

[Case TestAsc = 48 And TestAsc <= 57 Or TestAsc = 46]

The challenge is the code as written only captures the first digit after Operator.

At the end of the day, I would like only the numbers and "." for each true numeric constant in
strGiven to pass to the Case above. In strGiven the only true Numeric constants preceded by an
Operator a

Begining Full Numeric
1st Digit
-9 9
-7 7
-6 6
+2 28038
^3 35
^1 1
\1 <I'll remove this from consideration 123

Is there any easy way to capture the full numeric constant value?

Thank you again for your help!

Recapping:
1) How to correctly write the ReDim Preserve (with two elements)?
2) How to capture the full Numeric Constant in lieu of only 1st digit?
3) Any ideas on how to limit the strGiven characters to sucessfully pass to
the Case TestAsc = 48 And TestAsc <= 57 Or TestAsc = 46

EagleOne

"Jim Cone" wrote:

Not exactly clear, but try this...
(and "\" is an arithmetic operator)
---
Sub FigureItOut()
Dim N As Long
Dim M As Long
Dim x As Long
Dim strWhat As String
Dim strGiven As String
Dim vThings As Variant
Dim strArr() As String
ReDim strArr(1 To 100)

'some extras in the string
strGiven = "-9'Min. Int.'!F26-'Min.-7 Int.'!F31+28038^35+[C:\123]'Clos-6ing'!E3^1"
vThings = Array("-", "+", "^", "\", "/", "*")

M = 0
For N = 0 To UBound(vThings)
Do
M = InStr(M + 1, strGiven, vThings(N), vbBinaryCompare)
If M 0 Then
If Mid$(strGiven, M + 1, 1) Like "#" Then
strWhat = Mid$(strGiven, M, 2)
x = x + 1
strArr(x) = strWhat
End If
Else
Exit Do
End If
Loop
Next

ReDim Preserve strArr(1 To x)
Range("A1", Cells(1, x)).Value = strArr()
End Sub