Want some help to use VBA InStr() function

[DEvOTeEL]

Using Microsoft Office 2003 & 2007. ActiveCell.Formula ='P:\DMB\[MyWorkBook.xls]Allocation '!$U$77*1000000-_'P:\DMB\[MyWorkBook.xls]Allocation '!$U$85 . Current
VBA code to isolate the first cell reference follows:

BeginStr = Left(ActiveCell.Formula, InStr(ActiveCell.Formula, "!") +
1))
CellRefStr = InStr(BeginStr, ActiveCell.Formula, "*")
? CellRefStr yields $U$77 but only if the Operator is "*"

1) How best can I code "*" in the above InStr() so as to substitute it with ANY Operator? i.e., Operators = Array("/", "+", "-", "[*]", ">", "<", "=")
2) How then to yield $U$85 ?
Ultimately my End Game is to isolate the each Cell Reference (could be many!) after each WorkBook Worksheet reference in the ActiveCell.Formula. Because there could be many Cell References my initial approach above may well be inappropriate or the long way around the barn. Any help appreciated greatly.

[Shurod]

You may want to consider using the precedents and dependents property of the cell. I did look at yur code and I may have missed something obvious, but the "Precedents" property does not yield what I need when there is a link to another Workbook; Worksheet.

[Erakna]

It starts getting complicated if you go off sheet. That feature is on my to-do list. However, I may never get to it as the response to the Formula Map code was almost non-existent. Your code was a life-saver to me in 2006. I may have not communicated that to you at the time. BTW, my comment was/is not a guilt-trip nor a pressure tactic. Do you know any other reliable way to isolate even the first cell reference in an ActiveCell.Formula when there is a WorkBook and Worksheet link.

[GaganjyotTechie]

I should have mentioned that the following code works:

BeginStr = Len(Left(ActiveCell.Formula, InStr(ActiveCell.Formula,
"'!") + 1))
EndStr = InStr(BeginStr, ActiveCell.Formula, "?????????????")

If I could figure how to "replace" "?????????????" (above) with any Operator like + - * / = the challenge is solved. Is there a function which will give me the string position of any (or minimally the first) operator in ActiveCell.Formula.

[DanielaA]

I can't quite follow, but are there any ideas here that can help. I have set a vba library reference to the reference below.

Sub Demo()
'// = = = = = = = = = =
'// Microsoft VbScript Regular Expressions 5.5
'// = = = = = = = = = =

Dim Re
Dim MyEquation As String
Dim LookFor As String
Dim AllPositions
Dim Position

MyEquation = "=A1*B1/3.4+3-2"

'// Find all positions of + - * /
LookFor = "[\+ \- \* \/]"

Set Re = New RegExp
Re.Pattern = LookFor
Re.IgnoreCase = True
Re.Global = True

Set AllPositions = Re.Execute(MyEquation)

MsgBox "Found: " & AllPositions.Count

For Each Position In AllPositions ' 0-Indexed
Debug.Print Position.FirstIndex + 1
Next Position
End Sub


Returns:

Found: 4
4
7
11
13