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

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

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

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


On Fri, 8 Oct 2004 18:08:37 -0700, List Lurker
wrote:

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

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

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

--
Regards
Frank Kabel
Frankfurt, Germany


List Lurker wrote:
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

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

[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




In article , wwvlists-
says...
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

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

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

On Sun, 10 Oct 2004 15:46:15 -0700, List Lurker
wrote:

[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




In article , wwvlists-
says...
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