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


0




Reply

Utf

3/25/2010 5:17:03 PM 

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


0




Reply

Utf

3/25/2010 5:35:01 PM


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" <DoubleZ@discussions.microsoft.com> wrote in message
news:25C1DBFAB8D047DCBEE054541A6B4B26@microsoft.com...
> 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


0




Reply

Luke

3/25/2010 5:43:10 PM


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
<DoubleZ@discussions.microsoft.com> 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


0




Reply

Chip

3/25/2010 5:59:15 PM


Borrowing from Mike's post...
Function IsFormula(r As Range) As Boolean
IsFormula = r.HasFormula
End Function

Best Regards,
Luke M
"Luke M" <lukemoraga@nospam.com> wrote in message
news:OG4MkKEzKHA.928@TK2MSFTNGP05.phx.gbl...
> 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" <DoubleZ@discussions.microsoft.com> wrote in message
> news:25C1DBFAB8D047DCBEE054541A6B4B26@microsoft.com...
>> 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
>
>


0




Reply

Luke

3/25/2010 6:08:26 PM


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" <DoubleZ@discussions.microsoft.com> wrote in message
> news:25C1DBFAB8D047DCBEE054541A6B4B26@microsoft.com...
> > 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
>
>
> .
>


0




Reply

Utf

3/25/2010 6:27:01 PM



5 Replies
688 Views
(page loaded in 0.09 seconds)
