2003/2007

My goal is to ID both the Position [OprPosition(iCounter)] and the actual Operator sign
[OprSign(Icounter)] in that position.

My Formula string is:
='3 Period Average EBITDA Summary'!H8+ _
'3 Period Average EBITDA Summary'!H22+ _
'3 Period Average EBITDA Summary'!H23

The string position of the first "+" in the formula is 38.

Ideally, I would like the array elements to be:
OprPosition(38) to equal 38 and
OprSign(38) to equal "+"
(to me meaning - in the immediate window ? OprPosition(38) yields 38
(and ? OprSign yields "+")

My logic is that that iCounter AND OprSign AND OprPosition are the same.
Is there a better way to do this??

Sub Test()

Dim p As Long
Dim s As Long
Dim iCounter As Long
'
Dim OprSign() As String
ReDim OprSign(1 To Len(ActiveCell.Formula)) As String
ReDim OprPosition(1 To Len(ActiveCell.Formula)) As Long
OprSigns = Array("+", "-", "*", "/", "^", "", "<", "<", "=", "<=")

For iCounter = 1 To Len(ActiveCell.Formula) Step 1
For s = 0 To 9
'p = InStr(iCounter, Mid(ActiveCell.Formula, iCounter, 1), OprSigns(s), vbTextCompare)
If Mid(ActiveCell.Formula, iCounter, 1) = OprSigns(s) Then
OprPosition(iCounter) = iCounter ' this works fine
OpSign(iCounter) = OprSigns(s) ' this does not work
'OpSign = OprSigns(s)
' = OprSigns(s)
End If
'OprPosition(pCounter) = OperSigns
Next s
Next iCounter
End With

End Sub


TIA EagleOne