Hi there,

IS it possible to select all cells on the active sheet that contains
constants in my formulas?

EG:
=A1+A2 = do not select
=A1+A2+100 = select
=100*A1-A2 =select

(=A1+Max(3,4)= not select?!)



Quoting Ivan Maola's moto: Can do!
I hope you can show me ... :o)
Brgds Sige

Hi Sige,

Try something like:

'=================
Sub Tester01()
Dim rng As Range
Dim rng2 As Range
Dim rCell As Range
Dim arr As Variant
Dim sStr As String
Dim i As Long

arr = Array("/", "~*", "+", "-", "", "<", "=", "^")

On Error Resume Next 'In case no formulas!
Set rng = ActiveSheet.UsedRange.SpecialCells(xlFormulas)
On Error GoTo 0

If Not rng Is Nothing Then
For Each rCell In rng.Cells
For i = LBound(arr) To UBound(arr)
sStr = "*" & arr(i) & "[0-9]*"
If rCell.Formula Like sStr Then
If Not rng2 Is Nothing Then
Set rng2 = Union(rng2, rCell)
Else
Set rng2 = rCell
End If
End If
Next i
Next rCell
Else
'No formulas found
End If

rng2.Select

End Sub
'<<=================

---
Regards,
Norman



"Sige" wrote in message
oups.com...
Hi there,

IS it possible to select all cells on the active sheet that contains
constants in my formulas?

EG:
=A1+A2 = do not select
=A1+A2+100 = select
=100*A1-A2 =select

(=A1+Max(3,4)= not select?!)



Quoting Ivan Maola's moto: Can do!
I hope you can show me ... :o)
Brgds Sige

Wonderfull Norman!!!

Possible to catch these as well?

=60*A1 gets trapped but not

=A1*60

*-*-*-*-*-*-*-*-*-*-*
=100/+-A1 = OK
=A1/+-100 = OK

:o) Sige

Hi Sige,

Possible to catch these as well?

=60*A1 gets trapped but not

=A1*60

Change:

arr = Array("/", "~*", "+", "-", "", "<", "=", "^")

to:

arr = Array("/", "~*", "+", "-", "", "<", "=", "^", "[*]")


---
Regards,
Norman



"Sige" wrote in message
ps.com...
Wonderfull Norman!!!

Possible to catch these as well?

=60*A1 gets trapped but not

=A1*60

*-*-*-*-*-*-*-*-*-*-*
=100/+-A1 = OK
=A1/+-100 = OK

:o) Sige

Hi Sige,

arr = Array("/", "~*", "+", "-", "", "<", "=", "^", "[*]")

To add, the multiplication operator '*' is enclosed in square brackets so
that the Like operator can distingish it as a unique character, and not as
the Like operator's '*' wildcard character.

---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Sige,

Possible to catch these as well?

=60*A1 gets trapped but not

=A1*60

Change:

arr = Array("/", "~*", "+", "-", "", "<", "=", "^")

to:

arr = Array("/", "~*", "+", "-", "", "<", "=", "^", "[*]")


---
Regards,
Norman



"Sige" wrote in message
ps.com...
Wonderfull Norman!!!

Possible to catch these as well?

=60*A1 gets trapped but not

=A1*60

*-*-*-*-*-*-*-*-*-*-*
=100/+-A1 = OK
=A1/+-100 = OK

:o) Sige

Hi Norman,

To add, the multiplication operator '*' is enclosed in square brackets so
that the Like operator can distingish it as a unique character, and not as
the Like operator's '*' wildcard character.

Devious. Wouldn't you need to add "(" and ")" too?

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com