How to test if a specified cell contains a formula?

lovallee

Board Regular
Joined
Jul 8, 2002
Messages
220
Hello

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

Louis
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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
 
Upvote 0
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.
 
Upvote 0
Thank you!

I will use the following UDF

Code:
Function EstFormule(rng As Range) As Boolean

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

End Function

Louis
 
Upvote 0
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
 
Upvote 0
=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
 
Upvote 0
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):

Code:
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top