software

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.Editgt;Gotogt;Specialgt;Formulas

or

Editgt;Gotogt;Specialgt;Constants
quot;Ian Elliottquot; wrote:

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

Edit menu / Go To / special / formulasedThanks-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.quot;Duke Careyquot; wrote:

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

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 SubGord Dibben MS Excel MVP

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

gt;Thanks-but sorry, is there a worksheet function that returns a true or false
gt;depending on whether the cell or range has a function in it?
gt;Thank you.
gt;
gt;
gt;quot;Duke Careyquot; wrote:
gt;
gt;gt; Editgt;Gotogt;Specialgt;Formulas
gt;gt;
gt;gt; or
gt;gt;
gt;gt; Editgt;Gotogt;Specialgt;Constants
gt;gt;
gt;gt;
gt;gt;
gt;gt; quot;Ian Elliottquot; wrote:
gt;gt;
gt;gt; gt; Thanks for any help.
gt;gt; gt; I have a workbook with six tabs that are all formula. But sometimes I paste
gt;gt; gt; values over these formulas by mistake. I would like somehow to check quickly
gt;gt; gt; if the cells are formulas, or values. I could do this by selecting the cells
gt;gt; gt; one by one, and looking at the formula bar. But there are about 200-300
gt;gt; gt; cells, so this would take a couple minutes or so per tab.
gt;gt; gt; I could also write some code I think that checks each cell to see if it has
gt;gt; gt; a formula or not, and tell the user so.
gt;gt; gt; But preferably I think, I would like some worksheet function that could do
gt;gt; gt; this. Maybe have it check over a range. Are there any functions that can tell
gt;gt; gt; if a cell has a formula or is just a value? I looked at the CELL worksheet
gt;gt; gt; function, but it doesn't look like it can tell.
gt;gt; gt; Thanks very much.
gt;gt; gt;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:
www.mvps.org/dmcritchie/excel/getstarted.htm

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

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

quot;Ian Elliottquot; wrote:
gt; But preferably I think, I would like some worksheet function that could do
gt; this. Maybe have it check over a range. Are there any functions that can tell
gt; if a cell has a formula or is just a value? I looked at the CELL worksheet
gt; 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)
gt;gt;gt; Should produce gt;gt;gt; TRUE

Or you cound just enter: ? Range(quot;B4quot;).HasFormula (and press the enter
key)

HTH

quot;brit0nquot; gt; wrote in message
news

gt; I have searched for an answer to that exact question and can't find it. I
gt; wrote a macro to do it which is fine but, like you, not exactly what I wanted.
gt;
gt; To explain to those who didn't understand the question, I give the example
gt; of something which CAN be done but not quite the way it's wanted: I wanted to
gt; change cells which were formulas into their values. One of several ways to do
gt; it was to press F2, F9, Enter (edit formula, use F9 hotkey to change to value
gt; and leave value in cell). But that didn't work for a range of cells selected
gt; so I had to use a macro which simply copies the cells and uses paste special
gt; to paste all the values in.
gt;
gt; Your requirement has a similar problem. What you and I both want WOULD look
gt; something like this (in some cell other than A1):
gt;
gt; = IF ( ISFORMULA(A1), quot;A1 contains formulaquot;, quot;A1 doesn't contain
gt; formulaquot;)
gt;
gt; Unfortunately, there is no such spreadsheet function so yuo have to create a
gt; user-defined function to do it. That is basically what the other helpful
gt; answers have given you. Remember to include it with the workbook if you pass
gt; that book/sheet to anyone as they won't HAVE the user-defined function it
gt; requires.
gt;
gt; I did once find a way of doing it with embedded Excel functions.
gt; Unfortunately, I have searched all my active workbooks and I can't find it.
gt; And right now, I have some file corrupted which means the Excel help system
gt; won't get me there the way it did when I first used it and as I have an
gt; upgrade from Microsoft I decided to wait until I can install that which will
gt; fix my help system. I seem to remember that it basically used all the other
gt; IS...... functions to elimate all non-formula possibilities - heavy-handed,
gt; but it fit the requirement. You might want to recreate that one if you manage
gt; it before I find it again and post it. In the meantime, you have to use the
gt; user-defined function as given in the other posters' examples.
gt;
gt; (Don't forget that if you want simply to VIEW cells with formulas, you can
gt; use Edit-Go To-Special-Formulas. And if you want to use conditional
gt; formatting, you can use the user-defined function.)
gt;
gt; I don't know why Microsoft don't include ISFORMULA which imho is a lot more
gt; useful than ISREF !!!!!
gt;
gt; quot;Ian Elliottquot; wrote:
gt; gt; But preferably I think, I would like some worksheet function that could do
gt; gt; this. Maybe have it check over a range. Are there any functions that can tell
gt; gt; if a cell has a formula or is just a value? I looked at the CELL worksheet
gt; gt; function, but it doesn't look like it can tell.quot;JMayquot; wrote:

gt; 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 quot;IsFormula()quot;.

gt;reasonably simple SPREADSHEET FUNCTION

That depends on how you define quot;reasonably simplequot;!

To me, this is reasonably simple:

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

quot;brit0nquot; gt; wrote in message
...
gt; quot;JMayquot; wrote:
gt;
gt;gt; In VBA there is a property: HasFormula of the Range Object
gt;
gt; Thanks for that JMay. You referred to the same method as earlier responses
gt; including a complete user function. But no-one has definitively answered
gt; the
gt; precise question which was why I re-activated the thread.
gt;
gt; The question is whether or not there is a reasonably simple SPREADSHEET
gt; FUNCTION or combination of spreadsheets functions which can be used rather
gt; than a user defined function (using the HasFormula property).
gt;
gt; I guess we have to assume that for no known reason Microsoft decided not
gt; to
gt; include a built-in Spreadsheet function for quot;IsFormula()quot;.