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.

EditGotoSpecialFormulas

or

EditGotoSpecialConstants



"Ian Elliott" wrote:

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.

Edit menu / Go To / special / formulas


ed

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.


"Duke Carey" wrote:

EditGotoSpecialFormulas

or

EditGotoSpecialConstants



"Ian Elliott" wrote:

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.

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

On Mon, 24 Apr 2006 09:01:03 -0700, Ian Elliott
wrote:

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.


"Duke Carey" wrote:

EditGotoSpecialFormulas

or

EditGotoSpecialConstants



"Ian Elliott" wrote:

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.

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

Ian Elliott wrote:

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.

--

Dave Peterson

I have searched for an answer to that exact question and can't find it. I
wrote a macro to do it which is fine but, like you, not exactly what I wanted.

To explain to those who didn't understand the question, I give the example
of something which CAN be done but not quite the way it's wanted: I wanted to
change cells which were formulas into their values. One of several ways to do
it was to press F2, F9, Enter (edit formula, use F9 hotkey to change to value
and leave value in cell). But that didn't work for a range of cells selected
so I had to use a macro which simply copies the cells and uses paste special
to paste all the values in.

Your requirement has a similar problem. What you and I both want WOULD look
something like this (in some cell other than A1):

= IF ( ISFORMULA(A1), "A1 contains formula", "A1 doesn't contain
formula")

Unfortunately, there is no such spreadsheet function so yuo have to create a
user-defined function to do it. That is basically what the other helpful
answers have given you. Remember to include it with the workbook if you pass
that book/sheet to anyone as they won't HAVE the user-defined function it
requires.

I did once find a way of doing it with embedded Excel functions.
Unfortunately, I have searched all my active workbooks and I can't find it.
And right now, I have some file corrupted which means the Excel help system
won't get me there the way it did when I first used it and as I have an
upgrade from Microsoft I decided to wait until I can install that which will
fix my help system. I seem to remember that it basically used all the other
IS...... functions to elimate all non-formula possibilities - heavy-handed,
but it fit the requirement. You might want to recreate that one if you manage
it before I find it again and post it. In the meantime, you have to use the
user-defined function as given in the other posters' examples.

(Don't forget that if you want simply to VIEW cells with formulas, you can
use Edit-Go To-Special-Formulas. And if you want to use conditional
formatting, you can use the user-defined function.)

I don't know why Microsoft don't include ISFORMULA which imho is a lot more
useful than ISREF !!!!!

"Ian Elliott" wrote:
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.

In VBA there is a property: HasFormula of the Range Object

If cell B4 has =Sum(A1:A3)
Make B4 the Activecell
Switch to VBE - Control + G opens the Immediate Window

In it enter: ? Activecell.HasFormula (and press the enter key)
Should produce TRUE

Or you cound just enter: ? Range("B4").HasFormula (and press the enter
key)

HTH

"brit0n" wrote in message
:

I have searched for an answer to that exact question and can't find it. I
wrote a macro to do it which is fine but, like you, not exactly what I wanted.

To explain to those who didn't understand the question, I give the example
of something which CAN be done but not quite the way it's wanted: I wanted to
change cells which were formulas into their values. One of several ways to do
it was to press F2, F9, Enter (edit formula, use F9 hotkey to change to value
and leave value in cell). But that didn't work for a range of cells selected
so I had to use a macro which simply copies the cells and uses paste special
to paste all the values in.

Your requirement has a similar problem. What you and I both want WOULD look
something like this (in some cell other than A1):

= IF ( ISFORMULA(A1), "A1 contains formula", "A1 doesn't contain
formula")

Unfortunately, there is no such spreadsheet function so yuo have to create a
user-defined function to do it. That is basically what the other helpful
answers have given you. Remember to include it with the workbook if you pass
that book/sheet to anyone as they won't HAVE the user-defined function it
requires.

I did once find a way of doing it with embedded Excel functions.
Unfortunately, I have searched all my active workbooks and I can't find it.
And right now, I have some file corrupted which means the Excel help system
won't get me there the way it did when I first used it and as I have an
upgrade from Microsoft I decided to wait until I can install that which will
fix my help system. I seem to remember that it basically used all the other
IS...... functions to elimate all non-formula possibilities - heavy-handed,
but it fit the requirement. You might want to recreate that one if you manage
it before I find it again and post it. In the meantime, you have to use the
user-defined function as given in the other posters' examples.

(Don't forget that if you want simply to VIEW cells with formulas, you can
use Edit-Go To-Special-Formulas. And if you want to use conditional
formatting, you can use the user-defined function.)

I don't know why Microsoft don't include ISFORMULA which imho is a lot more
useful than ISREF !!!!!

"Ian Elliott" wrote:
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.

"JMay" wrote:

In VBA there is a property: HasFormula of the Range Object

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()".

reasonably simple SPREADSHEET FUNCTION

That depends on how you define "reasonably simple"!

To me, this is reasonably simple:

http://j-walk.com/ss/excel/usertips/tip045.htm

Although it describes how to use conditional formatting it can be used as a
worksheet function as well.

Biff

"brit0n" wrote in message
...
"JMay" wrote:

In VBA there is a property: HasFormula of the Range Object

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()".

Thanks T.V. That one was the easy solution given in many places in the
forums. But it simply is no use if you need a RESULT based on whether or not
a cell contains a formula or not.

I don't know about the original poster, but one of my needs (for which I use
a user defined function) is to enter in one cell a formula which will will
have one resultant value if ANOTHER cell (often on another sheet) contains a
formula and a different value if it doesn't. One case, for instance, has 2
sheets which contain information about different financial accounts. When
planning is in process, if money is planned to move from one account to
another, one of the sheets has a formula put in to calculate how much money
to move. The other sheet originally simply referred to that cell so it had
the same value. However, until that transfer was actually made (or ordered),
the second (receiving account) sheet was showing a credit which was only
tentative and all further balances were incorrect. Once the transfer was
made/ordered, the first sheet's cell would be converted to a value as it was
now a fixed amount.

Now, we have inserted the user function in the second cell which provides
the correct value if the first cell does NOT have a formula in it, otherwise
it contains 0 (zero) as no real transfer has been made. It means that when
the conversion is done on the first cell, we don't have to remember to go and
change the second cell.

Formatting cannot do this. The user function is fine, but as this is only
one of a number of shared spreadsheets, the function has to be included with
them all. If there was a Microsoft spreadsheet function, it would be
available to all users of all spreadsheets.

"T. Valko" wrote:

reasonably simple SPREADSHEET FUNCTION

That depends on how you define "reasonably simple"!

To me, this is reasonably simple:

http://j-walk.com/ss/excel/usertips/tip045.htm

Although it describes how to use conditional formatting it can be used as a
worksheet function as well.

Biff

"brit0n" wrote in message
...
"JMay" wrote:

In VBA there is a property: HasFormula of the Range Object

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()".