HOW-TO: Determine if a cell contains a number vs. formula vs. reference

[List Lurker]

Hi All:

I'm trying to create a scoring sheet to analyze the type of data used in
another sheet.

E.g., if a cell is blank (i.e., no data entered), I want to give it "0",
if the cell contains an explicit number that was entered, I'll give it a
"1", and if there is a reference, presumably to some total on yet
another, supplemental sheet, the I'll give it a "2".

I've played around w/ the "IS" functions, but ISREF seems not to do what
I would expect ... i.e., if a cell has 123 in it, both ISNUMBER and
ISREF give me TRUE ....

Is there someway to distinguish, as per this last example, between an
explicit number a formula/reference (i.e., something starting with an
equal sign)?

Any help +/or ideas would be much obliged,

Out

 

[Myrna Larson]

ISREF doesn't behave as I expect it to. It returns TRUE when the cell contains
a literal number.

If what you want to know is whether the cell contains a formula, this VBA
function will do that:

Function CellHasFormula(aRange As Range) As Boolean
CellHasFormula = aRange.Cells(1).HasFormula
End Function

 

[Frank Kabel]

Hi
for checking if the cell conatins a formula you'll need vBA. e.g. use
the following UDF:
public function is_formula(rng as range)
is_formula=rng.hasformula
end function

 

[List Lurker]

[This followup was posted to microsoft.public.excel.programming and a
copy was sent to the cited author.]

Myrna & Frank:

Got stuck reparing the sub-floor in my kitchen all weekend (the
dishwasher had gone out and there was water leakage damage!), so am just
getting back to the newgroup late Sunday afternoon.

Many thanks to both of you for the VBA snippet - don't know why I was so
hung up on trying to accomplish my task via a cell-based formula.

This looks exactly like what I need (although I haven't looked I'm
assuming there are similar HASxxxx type properties in VBA to check for
empties & numbers).

Thus, will wip-up some code and then have a command button on the
scoring sheet ... should do perfectly.

Again, thanks for your help.

Later

 

[Myrna Larson]

You're on the right track but they aren't properties and they don't beging
with "Has". You want the innate VBA function that begin with the word IS, i.e.

X = Cell.Value
If IsEmpty(X) Then
Msgbox "Empty"
ElseIf IsNumeric(X) Then
MsgBox "A number"
End If




IsEmpty and IsNumeric are what you are describing

[This followup was posted to microsoft.public.excel.programming and a
copy was sent to the cited author.]

Myrna & Frank:

Got stuck reparing the sub-floor in my kitchen all weekend (the
dishwasher had gone out and there was water leakage damage!), so am just
getting back to the newgroup late Sunday afternoon.

Many thanks to both of you for the VBA snippet - don't know why I was so
hung up on trying to accomplish my task via a cell-based formula.

This looks exactly like what I need (although I haven't looked I'm
assuming there are similar HASxxxx type properties in VBA to check for
empties & numbers).

Thus, will wip-up some code and then have a command button on the
scoring sheet ... should do perfectly.

Again, thanks for your help.

Later

Hi All:

I'm trying to create a scoring sheet to analyze the type of data used in
another sheet.

E.g., if a cell is blank (i.e., no data entered), I want to give it "0",
if the cell contains an explicit number that was entered, I'll give it a
"1", and if there is a reference, presumably to some total on yet
another, supplemental sheet, the I'll give it a "2".

I've played around w/ the "IS" functions, but ISREF seems not to do what
I would expect ... i.e., if a cell has 123 in it, both ISNUMBER and
ISREF give me TRUE ....

Is there someway to distinguish, as per this last example, between an
explicit number a formula/reference (i.e., something starting with an
equal sign)?

Any help +/or ideas would be much obliged,

Out