Formula to a number can you tell

S

scott

In cell a1 I have a formula. At times whole numbers are entered in this cell
replacing the formula. In a seperate cell is it possible to distinguish if
there is a formula or number in cell a1 using another formula. I do not want
to track changes I just would liek to know what is in cell a1

Thanks
Scott
 
D

Dave Peterson

You can create a userdefined function that returns true or false if the cell
contains a formula:

Option Explicit
Function HasFormula(rng As Range) As Boolean
Set rng = rng.Cells(1)
HasFormula = rng.HasFormula
End Function

Then you can include that test in your formula:

=hasformula(a1)

But if you start entering 5 as =5, then this won't work. It actually looks for
any old formula.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
C

chillihawk

Its possible to do this. I can't think of an inbuilt function to do it
however so you'd have to write your own, something like this:

Public Function isFormula(ByRef p_rngTgt as Range) as Boolean

isFormula = p_rngTgt.HasFormula

End Function

If you only need to know however then if you do CTRL+` you toggle
between formulas and values and you can just see.

HTH
 
O

Otto Moehrbach

Enter this UDF into a standard module. Then in some cell where you want the
indicator to be, enter "=IsFormula(A1)" without the quotes. The result
will be "True" if there is a formula in A1 and "False" if not. HTH Otto
Function IsFormula(r As Range) As Boolean

Application.Volatile True

IsFormula = r.HasFormula

End Function
 
Top