how tell if cell has formula

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

[Duke Carey]

Edit>Goto>Special>Formulas

or

Edit>Goto>Special>Constants



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

[edcosoft@sbcglobal.net]

Edit menu / Go To / special / formulas


ed

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


"Duke Carey" wrote:

> Edit>Goto>Special>Formulas
>
> or
>
> Edit>Goto>Special>Constants
>
>
>
> "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.
> >

[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

On Mon, 24 Apr 2006 09:01:03 -0700, Ian Elliott
<IanElliott@discussions.microsoft.com> 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:
>
>> Edit>Goto>Special>Formulas
>>
>> or
>>
>> Edit>Goto>Special>Constants
>>
>>
>>
>> "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]

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