Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ISBLANK for a Range
Hi There,
ISBLANK-function works only for a a single cell -to my knowledge-. Anyone has a UDF which can evaluate a range? Brgds Sige |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ISBLANK for a Range
Hi Sige,
Try: Application.CountA(Selection) = 0 --- Regards, Norman "Sige" wrote in message oups.com... Hi There, ISBLANK-function works only for a a single cell -to my knowledge-. Anyone has a UDF which can evaluate a range? Brgds Sige |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ISBLANK for a Range
Hi Sige,
As you asked for a UDF, try: Function RangeBlank(Rng As Range) As Boolean RangeBlank = Application.CountA(Rng) = 0 End Function --- Regards, Norman "Sige" wrote in message oups.com... Hi There, ISBLANK-function works only for a a single cell -to my knowledge-. Anyone has a UDF which can evaluate a range? Brgds Sige |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ISBLANK for a Range
Thx Norman!
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ISBLANK for a Range
why not just =If(counta(rng)=0,"Blank","Not Blank")
rather than encasulate it in a UDF -- Regards, Tom Ogilvy "Sige" wrote in message oups.com... Thx Norman! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
ISBLANK for a Range
Hi Norman,
That simple thx! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
ISBLANK for a Range
Indeed Tom, why not ;o)
PS: I remember there was a way to add a "sort-of-comment" in a formula... without affecting the formula. Something like eg.: =If(counta(rng)=0,"Blank","Not Blank") *T("This is function returns true or false when range is empty") or =If(counta(rng)=0,"Blank","Not Blank") +Text("This is function returns true or false when range is empty") or .... I have no idea anymore how it was ... |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
ISBLANK for a Range
=If(counta(rng)=0,"Blank","Not Blank") &Left("This is function returns . . .
",0) seems to work. -- Regards, Tom Ogilvy "Sige" wrote in message oups.com... Indeed Tom, why not ;o) PS: I remember there was a way to add a "sort-of-comment" in a formula... without affecting the formula. Something like eg.: =If(counta(rng)=0,"Blank","Not Blank") *T("This is function returns true or false when range is empty") or =If(counta(rng)=0,"Blank","Not Blank") +Text("This is function returns true or false when range is empty") or ... I have no idea anymore how it was ... |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
ISBLANK for a Range
Hi Tom,
Thx ...it is actually not a concatentate that I am looking for! ... I mean a function that does not produce an output in the cell (like multiplying with 1, not to affect the formula)... but that you can use to comment the formula in the cell itself...and which is only visible when you select the cell. Sige |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
ISBLANK for a Range
Hi. If a function returns a number (not a string), you can add zero without
affecting the answer. Was this what you were thinking of: =MAX(A1:A4)+N("Your comment here") HTH :) -- Dana DeLouis Win XP & Office 2003 "Sige" wrote in message ups.com... Hi Tom, Thx ...it is actually not a concatentate that I am looking for! ... I mean a function that does not produce an output in the cell (like multiplying with 1, not to affect the formula)... but that you can use to comment the formula in the cell itself...and which is only visible when you select the cell. Sige |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
ISBLANK for a Range
And just to add to Dana's suggestion...
If the formula returns text, you could use: ="This is a date " & text(a1,"mm/dd/yyyy") & text("your comment here",";;;") Dana DeLouis wrote: Hi. If a function returns a number (not a string), you can add zero without affecting the answer. Was this what you were thinking of: =MAX(A1:A4)+N("Your comment here") HTH :) -- Dana DeLouis Win XP & Office 2003 "Sige" wrote in message ups.com... Hi Tom, Thx ...it is actually not a concatentate that I am looking for! ... I mean a function that does not produce an output in the cell (like multiplying with 1, not to affect the formula)... but that you can use to comment the formula in the cell itself...and which is only visible when you select the cell. Sige -- Dave Peterson |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
ISBLANK for a Range
Hi Dana,
Was exactly where I was looking for! Thx Sige |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
ISBLANK for a Range
Hi Dave,
& text("your comment here",";;;") Was not what I was thinking about, but does the same imo ... Besides that it converts the cell to Text. Thx Sige |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
ISBLANK for a Range
Just for interest, using your example and Dana's solution:
=IF(COUNTA(A1:A7)=0,"Blank","Not Blank")+N("Your comment here") returns #Value. perhaps if you used an example that represented what you were trying to achieve, it would have been easier to provide an answer to suit. -- Regards, Tom Ogilvy "Sige" wrote in message oups.com... Hi Dana, Was exactly where I was looking for! Thx Sige |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
ISBLANK for a Range
Yep. That's why I included this line...
If the formula returns text, you could use: Sige wrote: Hi Dave, & text("your comment here",";;;") Was not what I was thinking about, but does the same imo ... Besides that it converts the cell to Text. Thx Sige -- Dave Peterson |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
ISBLANK for a Range
Even when the formula returns a number no?
=SUM(A1:A5)& TEXT("your comment here";";;;") =125&TEXT("your comment here";";;;") MVG Sige PS: Dave (alias Bob ;o) ), I would like to re-post my Outlining-problem as I do not get out of the woods with it. Any ideas how to formulate it ...to get your code working for plural-level outlining, working? |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
ISBLANK for a Range
Hi Tom,
It was something which I picked up a while ago .. but could not remember what formula it was. I thought it could be useful to explain myself - others in the formula what the formula does. & Learned now that the "Commenting" is not flawless... Thank You! It was just a general question. =IF(COUNTA(A1:A7)=0;"Blank";"Not Blank")+N("When cells are empty, this formula returns an error ;o)") Brgds Sige |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
ISBLANK for a Range
Nope.
If you want a number, use N(). If you want text, use text(...,";;;"). I still don't understand how your outlining would work if you were doing it manually. Sige wrote: Even when the formula returns a number no? =SUM(A1:A5)& TEXT("your comment here";";;;") =125&TEXT("your comment here";";;;") MVG Sige PS: Dave (alias Bob ;o) ), I would like to re-post my Outlining-problem as I do not get out of the woods with it. Any ideas how to formulate it ...to get your code working for plural-level outlining, working? -- Dave Peterson |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
ISBLANK for a Range
Hi Dave,
Could I send you an email ..with an example workbook in it? Cheers Sige |
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
ISBLANK for a Range
I think using the newsgroup is better.
Sige wrote: Hi Dave, Could I send you an email ..with an example workbook in it? Cheers Sige -- Dave Peterson |
#21
Posted to microsoft.public.excel.programming
|
|||
|
|||
ISBLANK for a Range
I ll give it a try again ...
Dave Peterson wrote: I think using the newsgroup is better. Sige wrote: Hi Dave, Could I send you an email ..with an example workbook in it? Cheers Sige -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
isblank | Excel Worksheet Functions | |||
Isblank | New Users to Excel | |||
IF AND ISBLANK | Excel Worksheet Functions | |||
isblank range | Excel Discussion (Misc queries) | |||
Can you test for a range (Q16:19) any cell is ISBLANK | Excel Programming |