Value or formula?
 

Refering to General Question

Does anyone know how to differentiate the cell formed by a value or a formula?
such as in cell a1 = 7 [value only] and in cell b1 = 3 + 4 [any formula]
If the cell contains value, then return 0, else
If the cell contains formula, then reurn 1.
In this case, a2 = 0 [return], because a1 contains value and
b2 = 1 [return], because b1 contains formula
Does anyone have any suggestion?
Thank you in advance
Eric

Value or formula?
 

You could use a custom function:

Function IsFormula(rng As Range) As Boolean
IsFormula = rng.HasFormula
End Function


syntax is
=Isformula(A1)

or
=--Isformula(A1)
to get a 1 or 0.

For more on macros and UDF's.
http://www.mvps.org/dmcritchie/excel/getstarted.htm


"Eric" wrote:

Refering to General Question

Does anyone know how to differentiate the cell formed by a value or a formula?
such as in cell a1 = 7 [value only] and in cell b1 = 3 + 4 [any formula]
If the cell contains value, then return 0, else
If the cell contains formula, then reurn 1.
In this case, a2 = 0 [return], because a1 contains value and
b2 = 1 [return], because b1 contains formula
Does anyone have any suggestion?
Thank you in advance
Eric

Value or formula?
 

Thank you for your reply
I find no IsFormula function under my excel, could you please tell me how to
add this function?
Thank you very much
Eric

"JMB" wrote:

You could use a custom function:

Function IsFormula(rng As Range) As Boolean
IsFormula = rng.HasFormula
End Function


syntax is
=Isformula(A1)

or
=--Isformula(A1)
to get a 1 or 0.

For more on macros and UDF's.
http://www.mvps.org/dmcritchie/excel/getstarted.htm


"Eric" wrote:

Refering to General Question

Does anyone know how to differentiate the cell formed by a value or a formula?
such as in cell a1 = 7 [value only] and in cell b1 = 3 + 4 [any formula]
If the cell contains value, then return 0, else
If the cell contains formula, then reurn 1.
In this case, a2 = 0 [return], because a1 contains value and
b2 = 1 [return], because b1 contains formula
Does anyone have any suggestion?
Thank you in advance
Eric

Value or formula?
 

It is a user-defined function (UDF). You will need to copy the code into a
Visual Basic module.

Follow the link for instructions. You will want to pay particular attention
to "Using someone else's macro (#havemacro)" which covers installing a macro
from a newsgroup.




"Eric" wrote:

Thank you for your reply
I find no IsFormula function under my excel, could you please tell me how to
add this function?
Thank you very much
Eric

"JMB" wrote:

You could use a custom function:

Function IsFormula(rng As Range) As Boolean
IsFormula = rng.HasFormula
End Function


syntax is
=Isformula(A1)

or
=--Isformula(A1)
to get a 1 or 0.

For more on macros and UDF's.
http://www.mvps.org/dmcritchie/excel/getstarted.htm


"Eric" wrote:

Refering to General Question

Does anyone know how to differentiate the cell formed by a value or a formula?
such as in cell a1 = 7 [value only] and in cell b1 = 3 + 4 [any formula]
If the cell contains value, then return 0, else
If the cell contains formula, then reurn 1.
In this case, a2 = 0 [return], because a1 contains value and
b2 = 1 [return], because b1 contains formula
Does anyone have any suggestion?
Thank you in advance
Eric