I'm sorry to do this to you, but it is after 4:00am in the morning here and
I'm going to sleep for the night... I'll look at this (and hopefully your
response to my previous message) in about 5 or 6 hours when I get up.

--
Rick (MVP - Excel)


"Bony Pony" wrote in message
...
My attempt to limit the processed range. I'm sure there is a more elegant
way :)

Function IsRefOnly(R As Range) As Boolean
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 precedent lookup
z = 0 ' counter for precedent 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
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


I've learnt a lot from this! Thank you.

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


"Bony Pony" wrote:

No - I can see currentregion will not work. It doesn't allow the
For each cel in Rng to fully replace. Dang!
--
"There are 10 types of people in this world. Those who understand Binary
and those who don''t ..."


Also - an amendment to the above post - for vlookup and hlookup, the ,1
is a
range modifyer OR a column or row identifyer. In Match, the ,0 ,1 or ,-1
is
valid but does different things.

I guess it is unrealistic to expect this to work without some creative
judgement orinput ... what do you think?

Kind regards,
Robert

"Bony Pony" wrote:

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
will
always
return True for that formula even if there is no numeric or
text
constants
in it. Here is a modified function which I believe works
correctly in
all
circumstances...

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 = R.Formula
For Each Rng In R.Precedents.Areas
For Each Cel In Rng
Fml = Replace(Fml, Cel.Row, "")
Next
Next
If Not Fml Like "*#*" Then IsRefOnly = True
End If
End Function

My suggestion in my parallel post for you to use these
immediately
before
your loop through the range returned by the SpecialCells
property ...

Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

and reset them after immediately after the loop and in any On
Error
trap
sections still holds.

--
Rick (MVP - Excel)


"Bony Pony" wrote in
message
...
Hi Rick,
This looks like it will do exactly what I want! Absolutely
awesome!
It
works like a charm. Fantastic result!

Thank you very VERY much!!

Kind regards,
Bony

"Rick Rothstein" wrote:

Here is a function that I am pretty sure does what you
want... it
tests
if a
*single* cell is "pure" in the sense you have described (no
text or
number
constants) returning True if it is and False if it is not.
Use it in
conjunction with the SpecialCells call you mentioned, looping
through
each
cell in the range it returns, testing each cell with the
function
and
highlighting in anyway you chose those cells for which the
function
returns
False...