1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Test for formula in cell in Excel

Discussion in 'Business Applications' started by panman, Jun 25, 2003.

Thread Status:
Not open for further replies.
Advertisement
  1. panman

    panman Thread Starter

    Joined:
    Jun 25, 2003
    Messages:
    2
    Is there a way to create an Excel formula that will test another cell for a formula or a value? I need a way to flag cells that have their formula replaced by a number, using the "conditional formatting" function. Trouble is, I can't figure out a way to test for this condition. Help!:confused:
     
  2. mrwendal

    mrwendal

    Joined:
    Jul 25, 2001
    Messages:
    377
    Hmm,

    The only way that I can think of is to do the test in a couple of stages.

    1) use a macro or a bit of vb coding to temporarily add an apostrophe ( ' ) to the cells in question. This enables formulae to be displayed in the cells as text.

    2) you can then use conditional formatting and apply a conditional format of something like

    Formula is =left($G3,1)="="

    (this is presuming cell G3 contains the formula or number to test)

    3) Then run another macro to remove the apostrophes from the original cells

    --

    There is more than likely an easier way - but this is the only way I could think of.
    Also, I'm sure that if the cells to test will be the same or in the same column, etc, then the whole process can be done within one simple macro.
     
  3. deej

    deej

    Joined:
    Jun 11, 2003
    Messages:
    152
    Hi panman

    Try this user-defined function - you need to enter it into the module section for the current workbook, using the Visual Basic Editor. If you're not sure how to do this please respond and I'll post (or e-mail if you prefer) some brief instructions.

    '-----------------------------------------------------------------------------------
    Function ISFORMULA(LCELL As Range) As Boolean

    Dim MYCELL As Range
    Set MYCELL = LCELL

    '** test for multiple cells in range - return false if multiple
    If MYCELL.Cells.Count > 1 Then
    MsgBox "Can only use ISFORMULA function on single cell range."
    ISFORMULA = False
    Exit Function
    End If

    '** test leftmost character of cell.formula property
    '** return true if '=' else return false
    If Left(MYCELL.Formula, 1) = "=" Then
    '** this bit minimises risk of being fooled
    '** by text string starting with '='
    If Trim(MYCELL.Value) = Trim(MYCELL.Formula) = False Then
    ISFORMULA = True
    Else
    ISFORMULA = False
    End If
    Else
    ISFORMULA = False
    End If

    End Function
    '-----------------------------------------------------------------------------------

    The function simply checks the 'Formula' property of a cell, if the cell contains a formula then the formula property returns the full text of the formula if not it returns the actual value in the cell (or an empty string if the cell is blank).

    The first character of the returned Formula property is tested to see if it is '='. If it is then the chances are that the cell holds a formula, I have attempted to minimise the risk of being fooled by a cell containing a text string begininning with '=' (see comments in VBA code).

    The function will return True if the cell holds a formula and False if it does not. Once you have created the function in your workbook module you can use it in a formula to test your cells - eg:

    =IF(ISFORMULA(A1) = TRUE,"Formula Present","No Formula")

    The Formula property invariably returns an error if applied to a range of more than one cell, so I have trapped this possibility to display an error message and return False if the range includes more than one cell.

    Hope it works for you!

    Deej, UK
     
  4. Bruce319

    Bruce319

    Joined:
    May 14, 2003
    Messages:
    379
    If your just checking to see if a cell has a formula for your own preferene and not just a value, then go to the Tools menu and choose options and click on Formulas in the view tab sheet.
    This will show any cell with a formula on it. Once you determined or fixed any errors, just unclick the formula button.
     
  5. Bruce319

    Bruce319

    Joined:
    May 14, 2003
    Messages:
    379
    I meant to say, that the excel will display the formula used for that cell in your spread sheet.
     
  6. deej

    deej

    Joined:
    Jun 11, 2003
    Messages:
    152
    Yes - the shortcut key combination is:

    Ctrl + '
     
  7. deej

    deej

    Joined:
    Jun 11, 2003
    Messages:
    152
    Apologies - shortcut key is:

    Ctrl + ` (top left of QWERTY key bank - left of '1')

    Deej
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/142336