Finding contents in a cell formula

[Guest]

Using XL 2003 & 97


Formula Cells are selected:
Selection.SpecialCells(xlCellTypeFormulas, 23).Select
............
............
As each cell is evaluated I need to know:

If the cell contains a plus sign (+) AND with the following (very next
position) being any digit (1234567890)
-OR-
If the cell contains a minus sign (-) AND with the following (very next
position) being any digit (1234567890)

-OR-
If the very first position in the cell formula is a digit

If any one of the above three possibilities is true then a result of "true"
or > 0 would work very well.

I know something about the string functions like MID() etc., but I do know
know how to weave the above three possibilities into an efficient and
accurate VBA loop.

Any thoughts would be helpful!

TIA

Dennis

 

[Guest]

Additional information:

In the third possibility, ( If the very first position in the cell formula
is a digit)
Should be revised to:

If the very first position in the cell formula after the equal (=) sign is a
digit.

What would be best is a Function that I could call but I'll take any help I
can get.

TIA Dennis

 

[Guest]

Hi Dennis
not tested but try:

sub foo
dim rng as range
dim cell as range
Dim pos as integer
dim sFormula as string

set rng = Selection.SpecialCells(xlCellTypeFormulas, 23)
for each cell in rng
sformula=cell.formula
pos=instr(sformula,"+")
if pos<len(sformula) then
if isumeric(mid(sformula,pos+1,1) then
'do something
end if
end if
'check the same for a minus sign
next
end sub

 

[Guest]

Frank,

Thanks for your time and knowledge.

You have given me an excellent foundation from which to build and adapt.

Dennis

 

[Dana DeLouis]

Would this work for you? I think it's close to what you are looking for. I
included a Trim function. You can remove it if you wish. I added it in
case you have a formula that looks like this: " =A1 + 5"

Function Check(rng As Range) As Boolean
Dim s
s = rng.Formula
s = WorksheetFunction.Trim(s)
Check = _
s Like "*+#*" Or _
s Like "*+ #*" Or _
s Like "*-#*" Or _
s Like "*- #*" Or _
s Like "=#*" Or _
s Like "= #*"
End Function


=Check(A1)
returns True or False

HTH

 

[Guest]

Short, sweet and clever. Excellent!

Thanks Dana and to Frank for all of the help.

Dennis

 

[Guest]

Dana,

I setup your function like follows. Did I get it correct?

Function Check(MyCell As Range) As Boolean
Dim myFormulaStr
myFormulaStr = MyCell.Formula
myFormulaStr = WorksheetFunction.Trim(myFormulaStr)
Check = _
myFormulaStr Like "*+#*" Or _
myFormulaStr Like "*+ #*" Or _
myFormulaStr Like "*-#*" Or _
myFormulaStr Like "*- #*" Or _
myFormulaStr Like "=#*" Or _
myFormulaStr Like "= #*"
End Function

The reason is I get the following error in the Sub Routine on the
Check(myCell)

"Compile error: ByRef argument type mismatch"

Hope that you see this!

TIA

Dennis


Sub FindFormulaCells2()
Dim MyCell, myRange As Range
Dim myFormulaStr As String
Dim PositionInmyFormula As Integer
Set myRange = Selection.SpecialCells(xlCellTypeFormulas, 23)
For Each MyCell In myRange
' Test for "="; "+"; and "=" each with a following numeric
myFormulaStr = MyCell.Formula
If Check(MyCell) Then
MyCell.Interior.ColorIndex = 8
End If
Next
End Sub

 

[Dana DeLouis]

Hello. When you get that "ByRef argument", here is a quick check to fix it.

Notice the function...
Function Check(MyCell As Range) As Boolean

and note this part...
...(MyCell As Range),,,

Now, in your Sub "FindFormulaCells2" you defined the following
Dim MyCell
This becomes a variant.

So, when you run ...Check(MyCell), you are passing a Variant variable,
but the function itself is expecting a "Range" variable. They don't match,
so you get an error.

In your Sub, try matching them:

Sub FindFormulaCells2()
Dim MyCell As Range
Dim myRange As Range
'....etc

Now, it should work. HTH.

 

[Guest]

Dana,

Resent this post as previous post did not make it.

Following is your function did I get it correct?

Function Check(MyCell As Range) As Boolean
Dim myFormulaStr
myFormulaStr = MyCell.Formula
myFormulaStr = WorksheetFunction.Trim(myFormulaStr)
Check = _
myFormulaStr Like "*+#*" Or _
myFormulaStr Like "*+ #*" Or _
myFormulaStr Like "*-#*" Or _
myFormulaStr Like "*- #*" Or _
myFormulaStr Like "=#*" Or _
myFormulaStr Like "= #*"
End Function

The reason ... I get the following error in the Sub Routine below:
(Specifically, the Check(myCell) where myCell is highlighted)

"Compile error: ByRef argument type mismatch"

Any thoughts?

TIA

Dennis

********************************************
Sub FindFormula() Dim MyCell, myRange As Range
' Dim myFormulaStr As String
Dim PositionInmyFormula As Integer
Set myRange = Selection.SpecialCells(xlCellTypeFormulas, 23)
For Each MyCell In myRange
' Test for "="; "+"; and "=" each with a following numeric
myFormulaStr = MyCell.Formula
If Check(MyCell) Then
MyCell.Interior.ColorIndex = 8
End If
Next
End Sub

 

[Guest]

Interesting!

I thought that
"Dim myCell, myRange as Range" Dim'ed both myCell AND myRange as Range

Therefore, based upon your comments;
As a result of the above Dim statement;
myCell is Dim'ed as a Variant
and myRange is Dim'ed as a Range

You are correct, the following works fine.
Dim myCell as Range
Dim myRange as Range

Dennis