how tell if cell has formula

I

Ian Elliott

Thanks for any help.
I have a workbook with six tabs that are all formula. But sometimes I paste
values over these formulas by mistake. I would like somehow to check quickly
if the cells are formulas, or values. I could do this by selecting the cells
one by one, and looking at the formula bar. But there are about 200-300
cells, so this would take a couple minutes or so per tab.
I could also write some code I think that checks each cell to see if it has
a formula or not, and tell the user so.
But preferably I think, I would like some worksheet function that could do
this. Maybe have it check over a range. Are there any functions that can tell
if a cell has a formula or is just a value? I looked at the CELL worksheet
function, but it doesn't look like it can tell.
Thanks very much.
 
I

Ian Elliott

Thanks-but sorry, is there a worksheet function that returns a true or false
depending on whether the cell or range has a function in it?
Thank you.
 
G

Gord Dibben

Ian

Couple of methods............

Function IsFormula(cell)
Application.Volatile
IsFormula = cell.HasFormula
End Function

usage is: =ISFORMULA(cellref) returns TRUE or FALSE

Alternate..................A macro to color cells with formulas.

Sub findformulas()
For Each cell In Selection
If cell.HasFormula Then
cell.Interior.ColorIndex = 3
End If
Next cell
End Sub


Gord Dibben MS Excel MVP
 
D

Dave Peterson

You can create a userdefined function that returns true or false if the cell
contains a formula:

Option Explicit
Function HasFormula(rng As Range) As Boolean
Set rng = rng.Cells(1)
HasFormula = rng.HasFormula
End Function

Then you can include that test in your formula:

=hasformula(a1)

But if you start entering 5 as =5, then this won't work. It actually looks for
any old formula.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
B

brit0n

JMay said:
In VBA there is a property: HasFormula of the Range Object
Click to expand...

Thanks for that JMay. You referred to the same method as earlier responses
including a complete user function. But no-one has definitively answered the
precise question which was why I re-activated the thread.

The question is whether or not there is a reasonably simple SPREADSHEET
FUNCTION or combination of spreadsheets functions which can be used rather
than a user defined function (using the HasFormula property).

I guess we have to assume that for no known reason Microsoft decided not to
include a built-in Spreadsheet function for "IsFormula()".
 
Top