Formula or not?

[Alan]

Hi,
Is there a formula to establish whether a cell contains a value or a
formula? What I mean is say A1 contained =10+20 so it shows 30 and it
contains a formula. If I then paste special values it still shows 30 but
there is no formula as there would not be in an empty cell. What I'm looking
for is a formula in another cell which will give a TRUE or FALSE if there is
a formula in A1 or not.
TIA,
Regards,
Alan.

[Niek Otten]

Hi Alan,

A simple User define Function (UDF) will do.

Function CellHasFormula(a As Range) As Boolean
CellHasFormula = a.HasFormula
End Function

Open the VB editor (ALT+F11)
From the menu: Insert>Module

Paste the above function in the module

Return to the worksheet. You can now use CellHasFormula(A1) as if it were a
built-in function.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"Alan" <alan111@ntlworld.com> wrote in message
news:eO5u2VzFFHA.2824@tk2msftngp13.phx.gbl...
> Hi,
> Is there a formula to establish whether a cell contains a value or a
> formula? What I mean is say A1 contained =10+20 so it shows 30 and it
> contains a formula. If I then paste special values it still shows 30 but
> there is no formula as there would not be in an empty cell. What I'm
> looking for is a formula in another cell which will give a TRUE or FALSE
> if there is a formula in A1 or not.
> TIA,
> Regards,
> Alan.
>

[Alan]

Hi Niek,
That is perfect, Thank You!
Once again my thanks to you and the experts in these newsgroups,
Regards,
Alan.

"Niek Otten" <nicolaus@xs4all.nl> wrote in message
news:%23wqcm00FFHA.3336@TK2MSFTNGP10.phx.gbl...
> Hi Alan,
>
> A simple User define Function (UDF) will do.
>
> Function CellHasFormula(a As Range) As Boolean
> CellHasFormula = a.HasFormula
> End Function
>
> Open the VB editor (ALT+F11)
> From the menu: Insert>Module
>
> Paste the above function in the module
>
> Return to the worksheet. You can now use CellHasFormula(A1) as if it were
> a built-in function.
>
> --
>
> Kind Regards,
>
> Niek Otten
>
> Microsoft MVP - Excel
>
> "Alan" <alan111@ntlworld.com> wrote in message
> news:eO5u2VzFFHA.2824@tk2msftngp13.phx.gbl...
>> Hi,
>> Is there a formula to establish whether a cell contains a value or a
>> formula? What I mean is say A1 contained =10+20 so it shows 30 and it
>> contains a formula. If I then paste special values it still shows 30 but
>> there is no formula as there would not be in an empty cell. What I'm
>> looking for is a formula in another cell which will give a TRUE or FALSE
>> if there is a formula in A1 or not.
>> TIA,
>> Regards,
>> Alan.
>>
>
>