Page 1 of 4 123 ... LastLast
Results 1 to 10 of 32

How to test if a specified cell contains a formula?

This is a discussion on How to test if a specified cell contains a formula? within the Excel Questions forums, part of the Question Forums category; Hello Which formula can I use to test if a specified cell contains a formula? Louis...

  1. #1
    Board Regular
    Join Date
    Jul 2002
    Location
    Quebec-Canada
    Posts
    201

    Default

    Hello

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

    Louis

  2. #2
    Board Regular
    Join Date
    May 2002
    Posts
    810

    Default

    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

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,809

    Default

    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.

  4. #4
    Board Regular
    Join Date
    Jul 2002
    Location
    Quebec-Canada
    Posts
    201

    Default

    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

  5. #5
    New Member
    Join Date
    Nov 2011
    Posts
    1

    Default Re: How to test if a specified cell contains a formula?

    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

  6. #6
    New Member
    Join Date
    Aug 2012
    Posts
    12

    Default Re: How to test if a specified cell contains a formula?

    Quote Originally Posted by Aladin Akyurek View Post
    =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

  7. #7
    Board Regular T. Valko's Avatar
    Join Date
    May 2009
    Location
    Pittsburgh
    Posts
    16,133

    Default Re: How to test if a specified cell contains a formula?

    Quote Originally Posted by DBrownBear View Post
    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.
    .
    Biff
    Microsoft MVP - Excel

    Using Excel 2002, 2007
    KISS - Keep It Simple Stupid

  8. #8
    New Member
    Join Date
    Aug 2012
    Posts
    12

    Default Re: How to test if a specified cell contains a formula?

    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

  9. #9
    Board Regular T. Valko's Avatar
    Join Date
    May 2009
    Location
    Pittsburgh
    Posts
    16,133

    Default Re: How to test if a specified cell contains a formula?

    Quote Originally Posted by DBrownBear View Post
    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.
    .
    Biff
    Microsoft MVP - Excel

    Using Excel 2002, 2007
    KISS - Keep It Simple Stupid

  10. #10
    New Member
    Join Date
    Aug 2012
    Posts
    12

    Default Re: How to test if a specified cell contains a formula?

    Thanks, Bill. Yes, that works GREAT. Dave

Page 1 of 4 123 ... LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com