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

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Steve Newhouse" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

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...

How about

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Steve Newhouse" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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...
>