Help with VBA InStr() function

E

EagleOne

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
 
J

Jim Cone

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" <[email protected]>
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
 
E

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, USAhttp://www.realezsites.com/bus/primitivesoftware

"EagleOne" <[email protected]>
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
Click to expand...

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
 
J

Jim Cone

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" <[email protected]>
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
 
E

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, USAhttp://www.realezsites.com/bus/primitivesoftware

"EagleOne" <[email protected]>
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
Click to expand...

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
 
E

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, USAhttp://www.realezsites.com/bus/primitivesoftware

"EagleOne" <[email protected]>
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
Click to expand...

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
 
J

Jim Cone

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" <[email protected]>
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
 
J

Jim Cone

This revised version should do a better job of finding the last operator.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Function OperateWhat_R1()
'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
End If
Next
lngTemp = 0
For N = 0 To 9
lngTemp = InStr(lngTemp + 1, ActiveCell.Formula, vSigns(N), vbTextCompare)
If lngTemp > 0 Then
If lngTemp > lngLast Then lngLast = lngTemp
End If
Next
If lngFirst <= lngLast Then
'?
Else
'?
End If
MsgBox lngFirst & vbCr & lngLast
End Function
'----------------
 
D

Dana DeLouis

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
 
D

Dana DeLouis

Here's my attempt at finding the two cell addresses.
There's not a lot of logic here. It just looks at the characters following
the "!" Character.
There may be a better technique. I shortened the string a little.

Returns:

Found: 2
$U$77
$U$85

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

Dim Re As RegExp
Dim MyEquation As String
Dim AllPositions As MatchCollection
Dim Position As Match

'// Looking for a cell Address:
Const LookFor As String = "![$ 0-9 A-Z]+"

MyEquation = "'P:\[Book.xls]Allo'!$U$77*10-'P:\DMB\[Book.xls]Allo'!$U$85"

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

If Not Re.Test(MyEquation) Then Exit Sub
Set AllPositions = Re.Execute(MyEquation)
Debug.Print "Found: " & AllPositions.Count

For Each Position In AllPositions ' 0-Indexed
Debug.Print Mid$(Position.Value, 2)
Next Position
End Sub

--
HTH :>)
Dana DeLouis
Windows XP & Office 2007

<snip>
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

non-VBA Alternative to SUBTITUTE function 6

Top