A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Function to determine if a cell contains a formula



 
 
Thread Tools Display Modes
  #1  
Old March 25th 10, 06:17 PM posted to microsoft.public.excel.misc
DoubleZ
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
Ads
  #2  
Old March 25th 10, 06:35 PM posted to microsoft.public.excel.misc
Mike H
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  
Old March 25th 10, 06:43 PM posted to microsoft.public.excel.misc
Luke M[_4_]
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  
Old March 25th 10, 06:59 PM posted to microsoft.public.excel.misc
Chip Pearson
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  
Old March 25th 10, 07:08 PM posted to microsoft.public.excel.misc
Luke M[_4_]
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  
Old March 25th 10, 07:27 PM posted to microsoft.public.excel.misc
DoubleZ
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

>
>
> .
>

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 03:16 PM.


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