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...

Function IsRefOnly(R As Range) As Boolean
Dim LCtext As String
Dim UCtext As String
Dim 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
If Not R.Formula Like "*[!A-Z]#*" Then IsRefOnly = True
End If
End Function