Identifying formulas (Excel 2000)

Topic

New Reply

I need to identify whether a cell contains a numeric constant or a function which returns a number. For example I want to distinguish between a cell that has 3 entered as a constant and a cell that has A1+B2 whose result is 3.

The functions (formulas) vary. They don’t all contain +,-,/ or *. But I think they do all contain at least one of these operators.

Any help would be much appreciated.

Reply | Quote

Replies

If you just want to select the cells with formulas, you can press F5 (or Ctrl-G), click on special, and then select Formulas. If you need to, you can further refine the selection by selecting Numeric, Text etc. When you have selected your requirements, click OK, and all the relevant cells should be selected.

Andrew C

Reply | Quote

Select all of the cells that you want to identify. Then from the Edit menu select “Go To”. In the dialog box click on the “Special…” button. In this dialog box select either “Constants” or “Formula” depending on which you want to identify. click on OK, and the cells should be selected.

Reply | Quote

In a variation on Post 113007, you could write a user defined function in a module:

Function IsNumericFormula(aCell As Range) As Boolean
IsNumericFormula = aCell.HasFormula And IsNumeric(aCell)
End Function

Then you can put a formula in a cell:

=IsNumericFormula(A5)

Reply | Quote

Thanks for all of your quick responses. I would like to use conditional formatting to show the difference between the cells easily, so the user defined formula seems to be the way to go.

However, I’m not quite sure how to set it up in the conditional formatting dialogue box. It seems that the the argument in the function would be nothing, but that doesn’t work.

Reply | Quote

Try this (based on the post I referred to earlier):

Function IsNumericFormula() As Boolean
IsNumericFormula = (Application.Caller.HasFormula) And IsNumeric(Application.Caller)
End Function

Now, in the conditional formatting dialog box, select Is Formula and in the condition box, enter
=IsNumericFormula()

Reply | Quote

Works great. Thanks.

Reply | Quote