Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I reference a cell as a row number in a formula? | Excel Discussion (Misc queries) | |||
Date formula to determine the number of days | Excel Worksheet Functions | |||
How to determine the cell contain number only? | Excel Discussion (Misc queries) | |||
Formula for Increasing a Cell Reference by a Given Number | Excel Worksheet Functions | |||
Formula to determine whether number falls within range?? | Excel Worksheet Functions |