ExcelBanter - Formula or not?

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Formula or not? (https://www.excelbanter.com/excel-worksheet-functions/14100-formula-not.html)

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.

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: InsertModule

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" wrote in message
...
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.

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

"Niek Otten" wrote in message
...
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: InsertModule

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" wrote in message
...
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.