Hi all,
I am in receipt of a workbook in which someone has "amended" random formulas
with harcoded additions - eg
=a1*b1*1.7
or
=if(a1=0,"None,"OK")

Does anyone have a vba approach to identify a cell that contains hardcoded
numbers or text?

Thanks in advance!
Bony

I'm not sure what you are trying to achieve, anyway, try this, don't
forget to change the range.
Code:
--------------------
Sub formulae()
Dim MyCell As Range
Dim C As String, D As String
For Each MyCell In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
If IsNumeric(MyCell) Then
C = C & vbLf & MyCell.Address
ElseIf MyCell.HasFormula And MyCell.Text < vbNullString Then
D = D & vbLf & MyCell.Address
End If
Next
MsgBox "Cells with Numeric values:" & vbLf & C & vbLf & vbLf _
& "Cells with Text values:" & vbLf & D
C = ""
D = ""
End Sub
--------------------


Bony Pony;440595 Wrote:
Hi all,
I am in receipt of a workbook in which someone has "amended" random
formulas
with harcoded additions - eg
=a1*b1*1.7
or
=if(a1=0,"None,"OK")

Does anyone have a vba approach to identify a cell that contains
hardcoded
numbers or text?

Thanks in advance!
Bony


--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=122224

Hi,
Thanks for 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.

Sorry - I though it was clear ....

Regards,
Bony

"Simon Lloyd" wrote:


I'm not sure what you are trying to achieve, anyway, try this, don't
forget to change the range.
Code:
--------------------
Sub formulae()
Dim MyCell As Range
Dim C As String, D As String
For Each MyCell In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
If IsNumeric(MyCell) Then
C = C & vbLf & MyCell.Address
ElseIf MyCell.HasFormula And MyCell.Text < vbNullString Then
D = D & vbLf & MyCell.Address
End If
Next
MsgBox "Cells with Numeric values:" & vbLf & C & vbLf & vbLf _
& "Cells with Text values:" & vbLf & D
C = ""
D = ""
End Sub
--------------------


Bony Pony;440595 Wrote:
Hi all,
I am in receipt of a workbook in which someone has "amended" random
formulas
with harcoded additions - eg
=a1*b1*1.7
or
=if(a1=0,"None,"OK")

Does anyone have a vba approach to identify a cell that contains
hardcoded
numbers or text?

Thanks in advance!
Bony


--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=122224

I believe what you were supplised was code that could help you find the cells
with formulas. Since formulas can typically contain what you're calling
"hard coded values", it's tough to know exactly what you are looking for.

Are you saying that for your IF Statement, you want something like this

=IF(A1=B2,A3,A4)

rather than
=IF(A1=0,"True","False")

I think it could take some time to program all of the permutations you may
need. If you want to find the cells with formulas, try this
'Untested
Dim aWS as excel.Worksheet
Dim myRange as excel.range
dim r as excel.range

Set aWS = ActiveSheet
for each r in aWS.usedrange
if r.hasformula then
ig myrange is nothing then
set myrange = r
else
set myrange = union(myrange,r)
end if
end if
next r

if not myrange is nothing then
myrange.select
myrange.interior.colorindex = 36 'Changes the highlight if you want it.
end if

Alternatively, you can use the Formula Auditing functionality to see all of
the formulas as written out.

HTH,
Barb Reinhardt
"Bony Pony" wrote:

Hi,
Thanks for 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.

Sorry - I though it was clear ....

Regards,
Bony

"Simon Lloyd" wrote:


I'm not sure what you are trying to achieve, anyway, try this, don't
forget to change the range.
Code:
--------------------
Sub formulae()
Dim MyCell As Range
Dim C As String, D As String
For Each MyCell In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
If IsNumeric(MyCell) Then
C = C & vbLf & MyCell.Address
ElseIf MyCell.HasFormula And MyCell.Text < vbNullString Then
D = D & vbLf & MyCell.Address
End If
Next
MsgBox "Cells with Numeric values:" & vbLf & C & vbLf & vbLf _
& "Cells with Text values:" & vbLf & D
C = ""
D = ""
End Sub
--------------------


Bony Pony;440595 Wrote:
Hi all,
I am in receipt of a workbook in which someone has "amended" random
formulas
with harcoded additions - eg
=a1*b1*1.7
or
=if(a1=0,"None,"OK")

Does anyone have a vba approach to identify a cell that contains
hardcoded
numbers or text?

Thanks in advance!
Bony


--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=122224

Hi and thanks for the code.

To find the cells that contain formulas, it is simple to use
Selection.SpecialCells(xlCellTypeformulas, 3).Select
which will return formulas that contain text or numbers.

However it detects ALL formulas that equate to numbers - which is logical.

I want to be able to interrogate the formula string e.g.

in cell C1 : =if(a1=b1,a1+100,a1-50)

and detect the 100 or the -50.

The reason I want to do this is the 100 or the -50 are hard coded constants
that unless you know they are there, will always affect the result of C1

I want to highlight C1 as a cell that contains a formula driven by literals.

The correct way to approach this would be:

Cell d1 : 100 (input value)
Cell d2 : -50 (input value)
cell c1 : =if(a1=b1,a1+d1,a1+d2)

in which case C1 would remain unhighlighted.

Clearer? :)

regards,
Bony


"Barb Reinhardt" wrote:

I believe what you were supplised was code that could help you find the cells
with formulas. Since formulas can typically contain what you're calling
"hard coded values", it's tough to know exactly what you are looking for.

Are you saying that for your IF Statement, you want something like this

=IF(A1=B2,A3,A4)

rather than
=IF(A1=0,"True","False")

I think it could take some time to program all of the permutations you may
need. If you want to find the cells with formulas, try this
'Untested
Dim aWS as excel.Worksheet
Dim myRange as excel.range
dim r as excel.range

Set aWS = ActiveSheet
for each r in aWS.usedrange
if r.hasformula then
ig myrange is nothing then
set myrange = r
else
set myrange = union(myrange,r)
end if
end if
next r

if not myrange is nothing then
myrange.select
myrange.interior.colorindex = 36 'Changes the highlight if you want it.
end if

Alternatively, you can use the Formula Auditing functionality to see all of
the formulas as written out.

HTH,
Barb Reinhardt
"Bony Pony" wrote:

Hi,
Thanks for 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.

Sorry - I though it was clear ....

Regards,
Bony

"Simon Lloyd" wrote:


I'm not sure what you are trying to achieve, anyway, try this, don't
forget to change the range.
Code:
--------------------
Sub formulae()
Dim MyCell As Range
Dim C As String, D As String
For Each MyCell In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
If IsNumeric(MyCell) Then
C = C & vbLf & MyCell.Address
ElseIf MyCell.HasFormula And MyCell.Text < vbNullString Then
D = D & vbLf & MyCell.Address
End If
Next
MsgBox "Cells with Numeric values:" & vbLf & C & vbLf & vbLf _
& "Cells with Text values:" & vbLf & D
C = ""
D = ""
End Sub
--------------------


Bony Pony;440595 Wrote:
Hi all,
I am in receipt of a workbook in which someone has "amended" random
formulas
with harcoded additions - eg
=a1*b1*1.7
or
=if(a1=0,"None,"OK")

Does anyone have a vba approach to identify a cell that contains
hardcoded
numbers or text?

Thanks in advance!
Bony


--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=122224

Sorry, I can't help right now. Maybe someone else can.

"Bony Pony" wrote:

Hi and thanks for the code.

To find the cells that contain formulas, it is simple to use
Selection.SpecialCells(xlCellTypeformulas, 3).Select
which will return formulas that contain text or numbers.

However it detects ALL formulas that equate to numbers - which is logical.

I want to be able to interrogate the formula string e.g.

in cell C1 : =if(a1=b1,a1+100,a1-50)

and detect the 100 or the -50.

The reason I want to do this is the 100 or the -50 are hard coded constants
that unless you know they are there, will always affect the result of C1

I want to highlight C1 as a cell that contains a formula driven by literals.

The correct way to approach this would be:

Cell d1 : 100 (input value)
Cell d2 : -50 (input value)
cell c1 : =if(a1=b1,a1+d1,a1+d2)

in which case C1 would remain unhighlighted.

Clearer? :)

regards,
Bony


"Barb Reinhardt" wrote:

I believe what you were supplised was code that could help you find the cells
with formulas. Since formulas can typically contain what you're calling
"hard coded values", it's tough to know exactly what you are looking for.

Are you saying that for your IF Statement, you want something like this

=IF(A1=B2,A3,A4)

rather than
=IF(A1=0,"True","False")

I think it could take some time to program all of the permutations you may
need. If you want to find the cells with formulas, try this
'Untested
Dim aWS as excel.Worksheet
Dim myRange as excel.range
dim r as excel.range

Set aWS = ActiveSheet
for each r in aWS.usedrange
if r.hasformula then
ig myrange is nothing then
set myrange = r
else
set myrange = union(myrange,r)
end if
end if
next r

if not myrange is nothing then
myrange.select
myrange.interior.colorindex = 36 'Changes the highlight if you want it.
end if

Alternatively, you can use the Formula Auditing functionality to see all of
the formulas as written out.

HTH,
Barb Reinhardt
"Bony Pony" wrote:

Hi,
Thanks for 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.

Sorry - I though it was clear ....

Regards,
Bony

"Simon Lloyd" wrote:


I'm not sure what you are trying to achieve, anyway, try this, don't
forget to change the range.
Code:
--------------------
Sub formulae()
Dim MyCell As Range
Dim C As String, D As String
For Each MyCell In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
If IsNumeric(MyCell) Then
C = C & vbLf & MyCell.Address
ElseIf MyCell.HasFormula And MyCell.Text < vbNullString Then
D = D & vbLf & MyCell.Address
End If
Next
MsgBox "Cells with Numeric values:" & vbLf & C & vbLf & vbLf _
& "Cells with Text values:" & vbLf & D
C = ""
D = ""
End Sub
--------------------


Bony Pony;440595 Wrote:
Hi all,
I am in receipt of a workbook in which someone has "amended" random
formulas
with harcoded additions - eg
=a1*b1*1.7
or
=if(a1=0,"None,"OK")

Does anyone have a vba approach to identify a cell that contains
hardcoded
numbers or text?

Thanks in advance!
Bony


--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=122224

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

--
Rick (MVP - Excel)


"Bony Pony" wrote in message
...
Hi and thanks for the code.

To find the cells that contain formulas, it is simple to use
Selection.SpecialCells(xlCellTypeformulas, 3).Select
which will return formulas that contain text or numbers.

However it detects ALL formulas that equate to numbers - which is logical.

I want to be able to interrogate the formula string e.g.

in cell C1 : =if(a1=b1,a1+100,a1-50)

and detect the 100 or the -50.

The reason I want to do this is the 100 or the -50 are hard coded
constants
that unless you know they are there, will always affect the result of C1

I want to highlight C1 as a cell that contains a formula driven by
literals.

The correct way to approach this would be:

Cell d1 : 100 (input value)
Cell d2 : -50 (input value)
cell c1 : =if(a1=b1,a1+d1,a1+d2)

in which case C1 would remain unhighlighted.

Clearer? :)

regards,
Bony


"Barb Reinhardt" wrote:

I believe what you were supplised was code that could help you find the
cells
with formulas. Since formulas can typically contain what you're calling
"hard coded values", it's tough to know exactly what you are looking for.

Are you saying that for your IF Statement, you want something like this

=IF(A1=B2,A3,A4)

rather than
=IF(A1=0,"True","False")

I think it could take some time to program all of the permutations you
may
need. If you want to find the cells with formulas, try this
'Untested
Dim aWS as excel.Worksheet
Dim myRange as excel.range
dim r as excel.range

Set aWS = ActiveSheet
for each r in aWS.usedrange
if r.hasformula then
ig myrange is nothing then
set myrange = r
else
set myrange = union(myrange,r)
end if
end if
next r

if not myrange is nothing then
myrange.select
myrange.interior.colorindex = 36 'Changes the highlight if you want
it.
end if

Alternatively, you can use the Formula Auditing functionality to see all
of
the formulas as written out.

HTH,
Barb Reinhardt
"Bony Pony" wrote:

Hi,
Thanks for 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.

Sorry - I though it was clear ....

Regards,
Bony

"Simon Lloyd" wrote:


I'm not sure what you are trying to achieve, anyway, try this, don't
forget to change the range.
Code:
--------------------
Sub formulae()
Dim MyCell As Range
Dim C As String, D As String
For Each MyCell In Range("A1:A" & Range("A" &
Rows.Count).End(xlUp).Row)
If IsNumeric(MyCell) Then
C = C & vbLf & MyCell.Address
ElseIf MyCell.HasFormula And MyCell.Text < vbNullString Then
D = D & vbLf & MyCell.Address
End If
Next
MsgBox "Cells with Numeric values:" & vbLf & C & vbLf & vbLf _
& "Cells with Text values:" & vbLf & D
C = ""
D = ""
End Sub
--------------------


Bony Pony;440595 Wrote:
Hi all,
I am in receipt of a workbook in which someone has "amended" random
formulas
with harcoded additions - eg
=a1*b1*1.7
or
=if(a1=0,"None,"OK")

Does anyone have a vba approach to identify a cell that contains
hardcoded
numbers or text?

Thanks in advance!
Bony


--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile:
http://www.thecodecage.com/forumz/member.php?userid=1
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=122224