A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Count rows with specific attributes in VBA



 
 
Thread Tools Display Modes
  #1  
Old February 22nd 06, 01:43 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default Count rows with specific attributes in VBA

Hi all,

I need to create a function that lets me pass in a range and a value
that will iterate through all the rows in the range, check a specific
column in each row for a specific value, and if it exists, add 1 to a
counter. I want to do this in VBA, NOT using "SUM(IF" or "COUNTIF("
etc because these have limitations that don't work for me.

So something like this:

Foo(A1:A200, "matchVal")

Where A1:A200 is the range (really the range of ROWS) to check and
"matchVal" is the value to match on in whatever the column is that I
specify within the code of the Foo function.

Basically, I do not know how to, while iterating through cells in a
Range, access a particular column and check it for a specific value.
Can someone help?

Thanks,

Steve

Ads
  #2  
Old February 22nd 06, 01:56 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default Count rows with specific attributes in VBA

Explain the limitations that SUMIF has, it is a better solution.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Steve Newhouse" > wrote in message
oups.com...
> Hi all,
>
> I need to create a function that lets me pass in a range and a value
> that will iterate through all the rows in the range, check a specific
> column in each row for a specific value, and if it exists, add 1 to a
> counter. I want to do this in VBA, NOT using "SUM(IF" or "COUNTIF("
> etc because these have limitations that don't work for me.
>
> So something like this:
>
> Foo(A1:A200, "matchVal")
>
> Where A1:A200 is the range (really the range of ROWS) to check and
> "matchVal" is the value to match on in whatever the column is that I
> specify within the code of the Foo function.
>
> Basically, I do not know how to, while iterating through cells in a
> Range, access a particular column and check it for a specific value.
> Can someone help?
>
> Thanks,
>
> Steve
>



  #3  
Old February 24th 06, 09:27 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default Count rows with specific attributes in VBA

The "Count Unique Text Elements" solution proposed by Microsoft....

"=SUM(IF(FREQUENCY(MATCH(A1:A10,A1:A10,0),MATCH(A1 :A10,A1:A10,0))>0,1))"

.... has a problem. It counts ONLY items that occur no more than once.
I want to count each item, but only the first occurrence. So if there
are three "X" values, I still want to count 1 of them, not 0, and for
that first one, fall through to the next conditional test in the
formula (joined by +, *, etc as is the custome w/array formulas). The
other problem is that the above code appears to return an array of the
size of the range + 1, which is annoying and means that subsequent
conditional tests have to use A1:A11 to prevent error...

  #4  
Old February 25th 06, 01:30 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default Count rows with specific attributes in VBA

How about

=SUMPRODUCT((A2:A200<>"")/COUNTIF(A2:A200,A2:A200&""))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Steve Newhouse" > wrote in message
ups.com...
> The "Count Unique Text Elements" solution proposed by Microsoft....
>
> "=SUM(IF(FREQUENCY(MATCH(A1:A10,A1:A10,0),MATCH(A1 :A10,A1:A10,0))>0,1))"
>
> ... has a problem. It counts ONLY items that occur no more than once.
> I want to count each item, but only the first occurrence. So if there
> are three "X" values, I still want to count 1 of them, not 0, and for
> that first one, fall through to the next conditional test in the
> formula (joined by +, *, etc as is the custome w/array formulas). The
> other problem is that the above code appears to return an array of the
> size of the range + 1, which is annoying and means that subsequent
> conditional tests have to use A1:A11 to prevent error...
>



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert rows based on specific value bob Excel Worksheet Functions 6 February 29th 08 07:11 PM
Count rows with specific date lakegoddess Excel Discussion (Misc queries) 3 August 9th 05 04:36 PM
Count number of times a specific number is displayed in a cell ran subs Excel Worksheet Functions 1 June 27th 05 05:01 PM
How do I move rows showing a specific field to other worksheets/. jmp Excel Discussion (Misc queries) 1 March 4th 05 05:42 AM
How do I move rows showing a specific field to other worksheets/. jmp Excel Discussion (Misc queries) 0 March 4th 05 04:27 AM


All times are GMT +1. The time now is 12:30 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.