Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 42
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,501
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 457
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 7,247
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 457
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 42
Default 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



.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Function to determine whether cell contains a formula? Don Excel Worksheet Functions 4 March 2nd 07 05:11 AM
Value of cell to determine range in MAX Function [email protected] Excel Worksheet Functions 8 February 18th 06 07:34 PM
Is there a way to determine which cell the function =min() used? Justin Excel Worksheet Functions 6 October 16th 05 01:14 PM
Function to determine if any cell in a range is contained in a given cell [email protected] Excel Worksheet Functions 3 February 7th 05 05:19 PM
Is there a function to determine whether a cell contains a formul. Christo Kriel Excel Worksheet Functions 1 November 20th 04 07:54 PM


All times are GMT +1. The time now is 06:21 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"