How to test if a specified cell contains a formula?

[lovallee]

Hello

Which formula can I use to test if a specified cell contains a formula?

Louis

 

[stevebausch]

After I searched Object Browser, this was located:
Directly from Help files:

Worksheets("Sheet1").Activate
Set rr = Application.InputBox( _
prompt:="Select a range on this worksheet", _
Type:=8)
If rr.HasFormula = True Then
MsgBox "Every cell in the selection contains a formula"
End If

 

[Aladin Akyurek]

On 2002-08-22 13:17, lovallee wrote:
Hello

Which formula can I use to test if a specified cell contains a formula?

Louis

=ExtCell("hasformula",A1)

returns 1 for yes, 0 for no.

Do a search on this site on ExtCell if interested adding it to your system.

 

[lovallee]

Thank you!

I will use the following UDF

Function EstFormule(rng As Range) As Boolean

EstFormule = 0
If rng.HasFormula = True Then
    EstFormule = 1
Else
End If

End Function

Louis

 

[shahab]

Hi,

Would you please give me more detailed VB UDF because I'm new in VB and have no idea about abbreviations. I can not read your UDF.
I'm using Excel 2010.

Regards,
Shahab
Using Excel 2010

 

[DBrownBear]

=ExtCell("hasformula",A1)

returns 1 for yes, 0 for no.

Do a search on this site on ExtCell if interested adding it to your system.

Hi, I'm new to the website, and don't see how to "Do a search on this site on ExtCell".

Could you tell me how to do that? Or is it your other URL site where that Code is at?

Thanks, Dave

 

[T. Valko]

Hi, I'm new to the website, and don't see how to "Do a search on this site on ExtCell".

Could you tell me how to do that? Or is it your other URL site where that Code is at?

Thanks, Dave

You can use a VBA UDF (user defined function):

Function IsFormula(cell_ref As Range) 
    IsFormula = cell_ref.HasFormula 
End Function

Then you'd use it just like any other worksheet function:

=IsFormula(A1)

This will return either TRUE or FALSE.

 

[DBrownBear]

Thank you.

I just tried this Code, and the result for both a cell with and without a formula was the error "#name?"

Thoughts, please?

Dave

 

[T. Valko]

Thank you.

I just tried this Code, and the result for both a cell with and without a formula was the error "#name?"

Thoughts, please?

Dave

You have to place that code in a general macro module.

Here's how I do it...

Right click any sheet tab
Select: View code
Goto the menu Insert>Module
Paste the code into the window that opens on the right
Close that application to return to Excel

Now, try the function.

Enter a simple formula in cell A1: =SUM(Z1:Z10)

Enter this formula in B1: =IsFormula(A1)

You should get a result of TRUE.

 

[DBrownBear]

Thanks, Bill. Yes, that works GREAT. Dave