Hi Rick,
As you can see below, I have incorporated all your suggestions. I think it
is doing all I can expect it to now.

With regards to your observation point on my best practice points of
including the ,1 or ,0 as a number .. the big 5 consultancies do not see the
,0 or ,-1 or ,1 switches as literals - they are part of the formula and
whereas 0 and can be seen as False or True, there is no substitute for -1 ..
:)

I was hoping you would answer my question regarding the Relpace code - is
there a way to mask the ,0 or ,1 so two statements can be resolved into 1?

Have a great day!

Code follows:
Sub rc_cell_integrity()
Dim R As Range, sdoit As String
Set R = ActiveCell
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
sdoit = IsRefOnly(R)
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationSemiautomatic
If sdoit = False Then
MsgBox "Cell contains hard codes"
Else
MsgBox "Cell is good"
End If
'ActiveCell.Offset(1, 0).Select
End Sub

Function IsRefOnly(R As Range) As Boolean
' Returns True if the cell is "pure" or false if the cell has embedded
numbers or operators
' Grateful thanks to Rick Rothstein
' v4
Dim X As Long, Rw As Long
Dim y As Integer, z As Integer
Dim Rng As Range, Cel As Range
Dim Fml As String, LCtext As String
Dim UCtext As String, OriginalFormula As String
y = 100 ' limit for dependent lookup
z = 0 ' counter for dependent loop
If R.Count 1 Then
err.Raise vbObjectError + 1001, "IsRefOnly Function", _
"Only one cell permitted in Range for this function!"
Exit Function
End If
OriginalFormula = R.Formula
R.Formula = LCase(R.Formula)
LCtext = R.Formula
R.Formula = UCase(R.Formula)
UCtext = R.Formula
R.Formula = OriginalFormula
If LCtext = UCtext Then
Fml = Application.ConvertFormula(R.Formula, xlA1, xlA1, True)
Fml = Replace(Fml, ",0", "") ' replace range lookup or match qualifyer
Fml = Replace(Fml, ",1", "") ' replace range lookup or match qualifyer
Fml = Replace(Fml, ",-1", "") ' replace range lookup or match qualifyer
For Each Rng In R.Precedents.Areas
For Each Cel In Rng
z = z + 1
If z y Then Exit For
If InStr(Fml, Cel.Address(True, True)) Then Fml = Replace( _
Fml, Cel.Address(True, True), "")

Next
Next
If Not Fml Like "*#*" Then
IsRefOnly = True
ElseIf Not Fml Like "*[!$]#*" Then
IsRefOnly = True
End If
End If
End Function


--
"There are 10 types of people in this world. Those who understand Binary
and those who don''t ..."


"Rick Rothstein" wrote:

I was kind of hoping you would have replied to my initial comment to the
quote I include from an earlier posting of yours and to question I asked
about whether you added the three Application property calls I mentioned in
an earlier post of mine so that I would know where we currently stand. Also,
did you try out the inner loop modification I proposed and, if you did, did
it help any?

--
Rick (MVP - Excel)


"Bony Pony" wrote in message
...
Sleep well!!
--
"There are 10 types of people in this world. Those who understand Binary
and those who don''t ..."


"Rick Rothstein" wrote:

I figured when you said this...

"What I am trying to achieve is to highligt those
formulas that contain a numerical value or text
value that amends the result of the formula in a
non best practice way. Best practice dictates
that formulas should not contain hard coded
elements. All elements pertaining to a formula
should have a linked basis."

in your 2nd posting, that it was your practice to never have a numerical
constant in any of your formulas. From your statement, I would have
expected
you to have the 0 and 1 in your examples stored in a cell and a cell
reference to them in your formula. If you are going to allow numerical
constants in certain situations, then I don't think you will be able to
achieve what you want 100% of the time... to do that, you would have to
duplicate the full Excel parser in code (which I think would be
considerable
in size).

As for using CurrentRegion in place of the Precedents... no, I don't
think
that would work at all for multiple, individual cells whose current
regions
could extend well beyond their single occurrences. Did you use the three
Application property calls I mentioned in a previous message inside your
own
code where I indicated they should go? Also, perhaps changing the inner
loop
to this would help speed things up...

For Each Cel In Rng
If InStr(Fml, Cell.Address(True, True)) Then Fml = Replace( _
Fml, Cel.Address(True, True), "")
Next

--
Rick (MVP - Excel)


"Bony Pony" wrote in message
...
Hi Rick,
A typical formula that returns False would be the following:
match(a1,a5:a10,0) for absolute match or
match(a1,a5:a10,1) for closest match - both formulas are valid.

The ,0 or ,1 gets detected as an event.. which is why I included the
replace(fml,",0","") to eliminate them as these switches are always 0
or
1.

I can see a problem with using precedents.areas though - if you are
analysing a formula =sum(a:f), stepping through each dependent - even
on
my
Core i7 920 - takes forever ... so I changes .areas to .currentregion
and
it
works faster. Is this ok?

Thnk you for the time you are spending on this!
Kind regards,
Robert
--
"There are 10 types of people in this world. Those who understand
Binary
and those who don''t ..."


"Rick Rothstein" wrote:

Here is one more modification that add the handling of partial/full
row
references and partial/full column references (such as 3:3, 5:12, A:A
and
D:M)...

Function IsRefOnly(R As Range) As Boolean
Dim X As Long, Rw As Long
Dim Rng As Range, Cel As Range
Dim Fml As String, LCtext As String
Dim UCtext As String, OriginalFormula As String
If R.Count 1 Then
Err.Raise vbObjectError + 1001, "IsRefOnly Function", _
"Only one cell permitted in Range for this function!"
Exit Function
End If
OriginalFormula = R.Formula
R.Formula = LCase(R.Formula)
LCtext = R.Formula
R.Formula = UCase(R.Formula)
UCtext = R.Formula
R.Formula = OriginalFormula
If LCtext = UCtext Then
Fml = Application.ConvertFormula(R.Formula, xlA1, xlA1, True)
For Each Rng In R.Precedents.Areas
For Each Cel In Rng
Fml = Replace(Fml, Cel.Address(True, True), "")
Next
Next
If Not Fml Like "*#*" Then
IsRefOnly = True
ElseIf Not Fml Like "*[!$]#*" Then
IsRefOnly = True
End If
End If
End Function

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in
message
...
Give me some examples of the formulas the function doesn't work with
and
I'll see if I can patch the code to account for them.

As for the last function I posted, it still has a flaw in it.
Because I
set it up to remove the row number of cell addresses, there is a
change
I
could be replacing a numerical constant as well. As an example,
3*Row("A3")... once I have identified A3, my code then replaced all
3's
in
the formula text... that would mean the 3 multiplier would be
removed
as
well, making it impossible for the code to see the 3 multiplier.
Here
is
some modified code that eliminates this flaw (which you can use
until
you
respond to my opening sentence)...

Function IsRefOnly(R As Range) As Boolean
Dim X As Long, Rw As Long
Dim Rng As Range, Cel As Range
Dim Fml As String, LCtext As String
Dim UCtext As String, OriginalFormula As String
If R.Count 1 Then
Err.Raise vbObjectError + 1001, "IsRefOnly Function", _
"Only one cell permitted in Range for this function!"
Exit Function
End If
OriginalFormula = R.Formula
R.Formula = LCase(R.Formula)
LCtext = R.Formula
R.Formula = UCase(R.Formula)
UCtext = R.Formula
R.Formula = OriginalFormula
If LCtext = UCtext Then
Fml = Application.ConvertFormula(R.Formula, xlA1, xlA1, True)
For Each Rng In R.Precedents.Areas
For Each Cel In Rng
Fml = Replace(Fml, Cel.Address(True, True), "")
Next
Next
If Not Fml Like "*#*" Then IsRefOnly = True
End If
End Function

--
Rick (MVP - Excel)


"Bony Pony" wrote in message
...
... interesting change to the second version ... I like how you
reference
the
precedents.areas - master stroke.

I did notice though that if you use either version of your function
with
a
formula that contains a range modifyer - e.g. match, it detects the
,1
or
,0
as a literal. So it seems to work for simple formulas but not for
formulas
with elements. sigh ...

So I tried this ...

Sub rc_cell_integrity()
Dim R As Range, sdoit As String
Set R = ActiveCell
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
sdoit = IsRefOnly(R)
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationSemiautomatic
If sdoit = False Then
MsgBox "Cell contains hard codes"
Else
MsgBox "Cell is good"
End If
End Sub

Function IsRefOnly(R As Range) As Boolean
' Returns True if the cell is "pure" or false if the cell has
embedded
numbers or operators
' Grateful thanks to Rick Rothstein
Dim X As Long, Rw As Long
Dim Rng As Range, Cel As Range
Dim Fml As String, LCtext As String
Dim UCtext As String, OriginalFormula As String
If R.Count 1 Then
err.Raise vbObjectError + 1001, "IsRefOnly Function", _
"Only one cell permitted in Range for this function!"
Exit Function
End If
OriginalFormula = R.Formula
R.Formula = LCase(R.Formula)
LCtext = R.Formula
R.Formula = UCase(R.Formula)
UCtext = R.Formula
R.Formula = OriginalFormula
If LCtext = UCtext Then
Fml = R.Formula
For Each Rng In R.Precedents.Areas
On Error Resume Next
Fml = Replace(Fml, ",0", "") ' replace
Fml = Replace(Fml, ",1", "") ' replace
For Each Cel In Rng
Debug.Print Fml
Fml = Replace(Fml, Cel.Row, "")
Next
Next
If Not Fml Like "*#*" Then IsRefOnly = True
End If
End Function

I spent a short time trying to mask the ,0 or ,1 so I could do it
in
one
statement but eh ...

This seems to work. It does what I want it to so once again many
thanks!!

Kins regards,
Robert

"Rick Rothstein" wrote:

....if you have a cell address with 2 or more digits in its row
number, the function will always return True for that formula
even if there is no numeric or text constants in it.

I misstated the flaw above... for row numbers of 2 or more digits,
the
function will always return **False** even if there is no numeric
or
text
constants in it.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in
message
...
Actually, the function I posted has a flaw in it... if you have
a
cell
address with 2 or more digits in its row number, the function