Test If A Cell Has A Formula of Value

[MikeG]

In my worksheet, Cell C3 has the Formula =SUM(A1:A10).

Sometimes a user will overwrite this formula and enter a number - e,g, 136.

In Cell C5, I would like to show a 1 if C3 still contains the formula, and a 2 if it contains a number.

I guess I'm looking for something like =ISFORMULA(C3), but I know this function does not exist.

Does anyone have a way of addressing this?

Thanks,

MikeG

 

[VoG]

Try this UDF in a regular module

Function fmla(r As Range) As Long
fmla = IIf(r.HasFormula, 1, 2)
End Function

Then use

=fmla(A1)

 

[Philip_Trick]

You could write a "ISFORMULA" function in VBA.

Function IsFormula(cellRef as Range) as Boolean

IsFormula = (Left(cellRef.Formula,1) = "=")

End Function

That function would return "TRUE" if the first part of the cell is "=" and would return false otherwise.

 

[MikeG]

You could write a "ISFORMULA" function in VBA.

Function IsFormula(cellRef as Range) as Boolean

IsFormula = (Left(cellRef.Formula,1) = "=")

End Function

That function would return "TRUE" if the first part of the cell is "=" and would return false otherwise.

Thanks Philip and VOG. Is there any trick so it can be done with VBA? If not, I will use VBA.

MikeG

 

[Scott Huish]

You could write a "ISFORMULA" function in VBA.

Code:


Function IsFormula(cellRef as Range) as BooleanIsFormula = (Left(cellRef.Formula,1) = "=")End Function
</PRE>
That function would return "TRUE" if the first part of the cell is "=" and would return false otherwise.

That would also return TRUE if = was the only thing in the cell.

Function IsFormula(r As Range) As Long
IsFormula = r.HasFormula + 2
End Function

 

[MikeG]

That would also return TRUE if = was the only thing in the cell.

Function IsFormula(r As Range) As Long
IsFormula = r.HasFormula + 2
End Function

Thanks HP.

Could you tell me where I should put the code? I did a right click on my worksheet tab and then did "View Code" and pasted it in - but the function is not available to me.

MikeG

 

[VoG]

They all go in a regular module. Press ALT + F11, select Module from the Insert menu and paste in the code.

 

[MikeG]

They all go in a regular module. Press ALT + F11, select Module from the Insert menu and paste in the code.

Thanks, as always, VoG