Remember Me?

#1
March 25th 10, 05:17 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Jul 2008 Posts: 42
Function to determine if a cell contains a formula

In excel 2007, is there a formula to determine whether or not a cell contains
a formula? Basically I want to do and If/Then function that will return a
certain answer if a cell contains a formula and will return a different
answer if the cell only contains text (that was manually entered in).

Thank you.
DoubleZ

#2
March 25th 10, 05:35 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Apr 2007 Posts: 11,501
Function to determine if a cell contains a formula

Hi,

How about a UDF. Alt + F11 to open VB editor. Right click 'this workbook'
and insert module and paste the code in

Call with

=isformula(A1)

Function IsFormula(cel As Range) As String
If cel.HasFormula Then
IsFormula = "It's a formula"
Else
IsFormula = "It's not a formula"
End If
End Function
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

"DoubleZ" wrote:

In excel 2007, is there a formula to determine whether or not a cell contains
a formula? Basically I want to do and If/Then function that will return a
certain answer if a cell contains a formula and will return a different
answer if the cell only contains text (that was manually entered in).

Thank you.
DoubleZ

#3
March 25th 10, 05:43 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Feb 2010 Posts: 457
Function to determine if a cell contains a formula

Unfortunately, no. You could use a short UDF to determine this though. To
install the UDF, open the VBE (Alt+F11) and goto Insert, module. Paste the
following in:

'==========
Function IsFormula(r As Range) As Boolean
IsFormula = Left(r.Formula, 1) = "="
End Function
'==========

Close the VBE, and back in your workbook, you can simply use the formula
=IsFormula(A1)
to check if a cell contains a formula or not.

--
Best Regards,

Luke M
"DoubleZ" wrote in message
...
In excel 2007, is there a formula to determine whether or not a cell
contains
a formula? Basically I want to do and If/Then function that will return a
certain answer if a cell contains a formula and will return a different
answer if the cell only contains text (that was manually entered in).

Thank you.
DoubleZ

#4
March 25th 10, 05:59 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 7,247
Function to determine if a cell contains a formula

For many years, the documentation for the CELL function stated that
you could use it to determine whether a cell has a formula, but it
never worked. So they changed the documentation.

You have to use a VBA function:

Public Function HasFormula(R As Range) As Boolean
HasFormula = R(1,1).HasFormula
End If

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

On Thu, 25 Mar 2010 10:17:03 -0700, DoubleZ
wrote:

In excel 2007, is there a formula to determine whether or not a cell contains
a formula? Basically I want to do and If/Then function that will return a
certain answer if a cell contains a formula and will return a different
answer if the cell only contains text (that was manually entered in).

Thank you.
DoubleZ

#5
March 25th 10, 06:08 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Feb 2010 Posts: 457
Function to determine if a cell contains a formula

Borrowing from Mike's post...

Function IsFormula(r As Range) As Boolean
IsFormula = r.HasFormula
End Function

--
Best Regards,

Luke M
"Luke M" wrote in message
...
Unfortunately, no. You could use a short UDF to determine this though. To
install the UDF, open the VBE (Alt+F11) and goto Insert, module. Paste the
following in:

'==========
Function IsFormula(r As Range) As Boolean
IsFormula = Left(r.Formula, 1) = "="
End Function
'==========

Close the VBE, and back in your workbook, you can simply use the formula
=IsFormula(A1)
to check if a cell contains a formula or not.

--
Best Regards,

Luke M
"DoubleZ" wrote in message
...
In excel 2007, is there a formula to determine whether or not a cell
contains
a formula? Basically I want to do and If/Then function that will return
a
certain answer if a cell contains a formula and will return a different
answer if the cell only contains text (that was manually entered in).

Thank you.
DoubleZ

#6
March 25th 10, 06:27 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Jul 2008 Posts: 42
Function to determine if a cell contains a formula

Thank you Mike and Luke! I am surprised to hear that Excel does not have
that functionality built in, but at least it isn't a difficult UDF.

Thanks again.
DoubleZ

"Luke M" wrote:

Unfortunately, no. You could use a short UDF to determine this though. To
install the UDF, open the VBE (Alt+F11) and goto Insert, module. Paste the
following in:

'==========
Function IsFormula(r As Range) As Boolean
IsFormula = Left(r.Formula, 1) = "="
End Function
'==========

Close the VBE, and back in your workbook, you can simply use the formula
=IsFormula(A1)
to check if a cell contains a formula or not.

--
Best Regards,

Luke M
"DoubleZ" wrote in message
...
In excel 2007, is there a formula to determine whether or not a cell
contains
a formula? Basically I want to do and If/Then function that will return a
certain answer if a cell contains a formula and will return a different
answer if the cell only contains text (that was manually entered in).

Thank you.
DoubleZ

.

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Don Excel Worksheet Functions 4 March 2nd 07 04:11 AM [email protected] Excel Worksheet Functions 8 February 18th 06 06:34 PM Justin Excel Worksheet Functions 6 October 16th 05 01:14 PM [email protected] Excel Worksheet Functions 3 February 7th 05 04:19 PM Christo Kriel Excel Worksheet Functions 1 November 20th 04 06:54 PM

All times are GMT +1. The time now is 08:11 PM.