gangof4@violet.berkeley.edu (john steggall) (11/01/89)
Dear friends, I've got a *really* simple problem with an Excel macro. I've had very little experience doing excel macros and have found little useful documentation in the excel docs or books I've seen (anyone have a suggestion for books on excel macros?) Anyway, here's the problem: 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. I can do it if I create a database of the cells in question but this is not appropriate for my particular spreadsheet. I've spent numerous hours on this one and haven't had any luck. I think that I must be doing something really stupid because I can't do anything with a selection range, at least with the macro. This is driving me crazy and holding up *vital* research (ha!). Any ideas? - john steggall - gangof4@violet.berkeley.edu (415) 643-8927 (lab)
barry@playfair.STANFORD.EDU (barry) (11/02/89)
In article <1989Nov1.092420.9034@agate.berkeley.edu> gangof4@violet.berkeley.edu (john steggall) writes: >I've got a *really* simple problem with an Excel macro. [...] >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. [...] > >- john steggall - >gangof4@violet.berkeley.edu >(415) 643-8927 (lab) You probably need to modify the =ARGUMENT() line of your macro to indicate that the argument is an array (e.g. =ARGUMENT("range",64), and also use an array calculation (e.g. {=COUNT(range)}) for your calculations. See The Functions & Macros manual, "Using Arrays in Function Macros", p. 128. Barry Eynon barry@playfair.stanford.edu -ARPA ...!decwrl!shasta!playfair!barry -USENET
neilh@microsoft.UUCP (Neil Hoopman) (11/05/89)
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? ----------------------------------------------------------------------------