Xref: utzoo comp.sys.mac.programmer:10202 comp.sys.mac:41649
Path: utzoo!censor!geac!jtsv16!uunet!microsoft!neilh
From: neilh@microsoft.UUCP (Neil Hoopman)
Newsgroups: comp.sys.mac.programmer,comp.sys.mac
Subject: Re: Simple Excel Macro Problem
Keywords: excel, macro
Message-ID: <8611@microsoft.UUCP>
Date: 4 Nov 89 17:54:25 GMT
References: <1989Nov1.092420.9034@agate.berkeley.edu>
Reply-To: neilh@microsoft.UUCP (Neil Hoopman)
Distribution: usa
Organization: Microsoft Corp., Redmond WA
Lines: 52

In article <1989Nov1.092420.9034@agate.berkeley.edu> (john steggall) writes:
> 
>I'd like to pass the macro a range of cells (e.g. A1:A50) and have it 
>count the number of cells which contain a specific value and then return
>that number.  Specifically, I'm trying to count the number of cells
>that have zeros, in a single column of data.
> 

Well, the first thing to look at is how to do this on a worksheet.  Many 
people aren't familiar with all the neat things that you can do with
array formulas.  To do what you stated above on a worksheet, type this
formula into a cell on the same worksheet:

=sum(if(a1:a50=0,if(a1:a50<>"",1,0),0))

Then, after you enter the formula, press Command-Enter (on the keypad).
One thing to note is that the second IF is only necessary if you are
searching for cells that are equal to zero.  You need it because blank
cells also have the value of zero.

If you were looking for values over 100, the formula would be simpler:

=sum(if(a1:a50>100,1,0))  (pressing Command-Enter)

The easiest way to do this in a macro is not to call a function macro
at all, but just to use the formula:

=sum(if(selection()=0,if(selection()<>"",0,1),0))  (Cmd-Enter)

If you REALLY want a function macro to do this, use this:
  ______________________________________________________
_|_________________________A____________________________|
1|  =ARGUMENT("array", 8)
2|  =SUM(IF(array=0,if(array<>"",0,1),0))  (Cmd-Enter)
3|  =RETURN(A2)

(Do I win any awards for best ASCII spreadsheet??)

The "8" in the argument statement says that you are passing a
reference.  Then, you can use the name "array" just as you would
use a reference in your function macro.

I hope this helps!  (If not, don't shoot me; I'm in the word
processing unit!)

----------------------------------------------------------------------------
Neil Hoopman - Microsoft Corp.                         uunet!microsoft!neilh
------------------------------                  microsoft!neilh@uunet.UU.NET
"Carpe Diem. Seize the day.         microsoft!neilh@beaver.cs.washington.edu
 Make your lives extraordinary."    ----------------------------------------
 - Dead Poets Society                  Neil?  Neil who?  Posted what?  When?
----------------------------------------------------------------------------