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!

EagleOne

You may want to consider using the precedents and dependents property
of the cell. You can see how I did it in my Formula Map program
in this post... http://tinyurl.com/yedjlw
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"EagleOne"
wrote in message
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!
EagleOne

On Apr 11, 12:10 pm, "Jim Cone" wrote:
You may want to consider using the precedents and dependents property
of the cell. You can see how I did it in my Formula Map program
in this post...http://tinyurl.com/yedjlw
--
Jim Cone
San Francisco, USAhttp://www.realezsites.com/bus/primitivesoftware

"EagleOne"
wrote in message
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!
EagleOne

Jim,

Thanks for your time and thoughts.

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.

EagleOne

That is correct.
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.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"EagleOne"
wrote in message
Jim,
Thanks for your time and thoughts.
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.
EagleOne

On Apr 11, 1:06 pm, "Jim Cone" wrote:
That is correct.
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.
--
Jim Cone
San Francisco, USAhttp://www.realezsites.com/bus/primitivesoftware

"EagleOne"
wrote in message
Jim,
Thanks for your time and thoughts.
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.
EagleOne

Jim,

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?

EagleOne

On Apr 11, 1:06 pm, "Jim Cone" wrote:
That is correct.
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.
--
Jim Cone
San Francisco, USAhttp://www.realezsites.com/bus/primitivesoftware

"EagleOne"
wrote in message
Jim,
Thanks for your time and thoughts.
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.
EagleOne

Jim,

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?

EagleOne

EO,
If this is any help, would you at least consider not bottom posting? <g
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Function OperateWhat()
'Finds the position of the first and last operator in the active cell formula.
'Jim Cone - San Francisco - April 2007
Dim vSigns As Variant
Dim lngFirst As Long
Dim lngLast As Long
Dim lngTemp As Long
Dim N As Long

vSigns = Array("+", "-", "*", "/", "^", "", "<", "<", "=", "<=")
lngFirst = Len(ActiveCell.Formula)
For N = 0 To 9
lngTemp = InStr(1, ActiveCell.Formula, vSigns(N), vbTextCompare)
If lngTemp 0 Then
If lngTemp < lngFirst Then lngFirst = lngTemp
If lngTemp lngLast Then lngLast = lngTemp
End If
Next
If lngFirst <= lngLast Then
'?
Else
'?
End If
MsgBox lngFirst & vbCr & lngLast
End Function
'----------------


"EagleOne"
wrote in message
Jim,
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?
EagleOne