VBA Countif from a named range cell to a specific row

**wotadude** · 07-17-2009, 12:11 AM

i require a UDF that i can specify a string that identifies a specific named range cell (named MyRefCell)from which to offset by +1 row for start of countif range row/column to another named range row to offset by -1 row as the last cell in the column of the start cell.
To return true if match > 0 (visible cells only).

eg. =OffsetVisCountif("MyRefCell","Match this value")

Refer attached sample workbook.

MyRefCell is the the name of the reference cell (cell B2) to offset from by 1 row in that column.
StopHere is the named range of the row (Row 11) to offset by -1 row
eg. the countif range would be B3:B10
count of match value ("Match this value") is 3, 2 are visible (visible greater than 0), so return true.

i hope that makes sense.
your help would be appreciated

**DonkeyOte** · 07-17-2009, 03:40 AM

If you're running a version of XL >= 2003 you can actually do this using a formula, albeit a Volatile Sumproduct:

Please Login or Register  to view this content.

obviously you may need to tweak this if column B is not a constant

In UDF terms the following might work ?

Please Login or Register  to view this content.

Called from a cell along the lines of:

=OFFSETVISCOUNT(MyRefCell,StopHere,"Match this Value")

**wotadude** · 07-19-2009, 04:37 PM

Cheers Donkeyote, that works a treat.
Out of curiosity, I havent seen the asterisk (*) used before in this fashion. Does this perform the equivalent of AND in this context ?

**wotadude** · 07-19-2009, 05:06 PM

Perhaps we arent quite there yet after all.
This appears to only work on the activesheet.
If i place the function in a formula on a different sheet to that of the data range it does not work.

**Leith Ross** · 07-20-2009, 01:12 AM

Hello Wotadude,

You just need a simple tweak to Donkey's code...

Please Login or Register  to view this content.

Range assumes you are referring to the ActiveSheet. In your case you want to refer to the worksheet of the named range. The Parent property of a range refers to the worksheet on which it resides.

**wotadude** · 07-20-2009, 05:11 PM

Thanks Leith.
I must remember use of the Range Parent reference.
However i found that this still produced an error (Application-defined or object-defined error).
So modified it to -

Please Login or Register  to view this content.

So i assume this should now work as needed.
Out of curiosity, I havent seen the asterisk (*) used before in this fashion. Does this perform the equivalent of AND in this context ?

**Leith Ross** · 07-20-2009, 06:24 PM

Hello Wotadude,

Yes, it is like using the boolean "And" operator. Each expression in parentheses evaluates to either a True or False. In VBA True and False have the numerical equivalents of -1 and 0 respectively. Mathematically multiplying any number by zero results in zero or in Boolean algebra - False.