Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Test for formula in cell in Excel


(!)

panman's Avatar
panman panman is offline
Junior Member with 2 posts.
THREAD STARTER
 
Join Date: Jun 2003
25-Jun-2003, 11:31 PM #1
Cool Test for formula in cell in Excel
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!
mrwendal's Avatar
mrwendal mrwendal is offline
Member with 377 posts.
 
Join Date: Jul 2001
Location: England
26-Jun-2003, 04:57 AM #2
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.
deej's Avatar
deej deej is offline
Member with 152 posts.
 
Join Date: Jun 2003
Location: UK
26-Jun-2003, 08:23 PM #3
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
Bruce319's Avatar
Bruce319 Bruce319 is offline
Member with 379 posts.
 
Join Date: May 2003
27-Jun-2003, 01:03 PM #4
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.
Bruce319's Avatar
Bruce319 Bruce319 is offline
Member with 379 posts.
 
Join Date: May 2003
27-Jun-2003, 01:05 PM #5
I meant to say, that the excel will display the formula used for that cell in your spread sheet.
deej's Avatar
deej deej is offline
Member with 152 posts.
 
Join Date: Jun 2003
Location: UK
27-Jun-2003, 02:05 PM #6
Yes - the shortcut key combination is:

Ctrl + '
deej's Avatar
deej deej is offline
Member with 152 posts.
 
Join Date: Jun 2003
Location: UK
28-Jun-2003, 06:14 AM #7
Apologies - shortcut key is:

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

Deej
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑