VBA to replace COUNTIFS with INDIRECT reference to Dynamic Cells via Defined Names

JBC007 · May 22, 2011

Hi,

I am new to VBA and I need your help. I use Excel 2010, and would like to create a UDF to replace a CountIfs (with “s” at the end) when using 3 conditions.
The trick is that the arguments that I need to use are defined names which themselves refer to ranges which may change dynamically.
Thank you...

***Overview***
As a formula in a cell, I have:
= COUNTIFS(INDIRECT(List1),Criteria1,INDIRECT(List2),Criteria2, INDIRECT(List3),Criteria3)
I want to transform this into VBA...

***Details***

\\The Ranges

“List1” is a defined name that refers to a cell (let’s call it “A1”) in the worksheet “Parameters”.
The cell “A1” has a formula that results in a specific string depending on whatever the user is doing elsewhere.
This specific string may vary. However, this string will always be the defined name of a range.

For instance: “Data_ID” which is the defined name for the range “B1:B100” in the worksheet “Data”, or “Register_Name” which is the defined name for the range “C1:C100” in the worksheet “Register”, and so on.

So, in a way: “List1” contains the string of a defined name that refers to Parameters[A1] which contains ”Data_ID” which refers to Data[B1:B100].

Similarly, “List2” refers to cell “A2” in the worksheet “Parameters”, and “List3” refers to cell “A3” in the worksheet “Parameters”.
“List1”, “List2” and “List3” are ranges of the same length (e.g. each have 100 rows).

Also, each row in each range have a meaningful relationship: e.g. ID#1 can be found in Row(1) of “List1”, as well as in Row(1) of “List2”, as well as in Row(1) of “List3”.

\\The Criteria

“Criteria1” is a defined name that refers to a cell (let’s call it “B1”) in the worksheet “Parameters”.
The cell “B1” has a formula that results in a specific string depending on whatever the user is doing elsewhere.
This specific string may vary. However, this string will always be something that:
- Either could be found in “List1”, such as “Product x”;
- Or could be used to compare to what is found in “List1”, such as “>0”.

So, in a way: “Criteria1” contains the string of a defined name that refers to Parameters[B1] which contains “Product x”.

Similarly, “Criteria2” refers to cell “B2” in the worksheet “Parameters”, and “Criteria3” refers to cell “B3” in the worksheet “Parameters”.

***Variation ***
I will not use this UDF always in the same way on every cells where I will call it.
For instance, sometimes I may need to pass “List1” directly as a defined name.
As per the example above, in this case “List1” will contain the defined name “Data_ID” which refers to Data[B1:B100] (without going through the intermediary cell reference).
Where I will use the above, I will pass “Criteria1” directly as a cell reference and not as a defined name.
As per the example above, in this case “Criteria1” will contain $A$1 (without going through the intermediary cell reference).
I don’t mind creating another UDF for that variation...

*** My last failed attempt***

Function COUNTIFS_3CONDITIONS(List1 As String, Criteria1 As String, List2 As String, Criteria2 As String, List3 As String, Criteria3 As String)
'List1, List2, List3 have each the same number of rows

Dim Row As Range
Dim IndirectList1 As Range
Dim IndirectList2 As Range
Dim IndirectList3 As Range

Set IndirectList1 = Range(Evaluate(Range(List1).Value)).Value
Set IndirectList2 = Range(Evaluate(Range(List2).Value)).Value
Set IndirectList3 = Range(Evaluate(Range(List3).Value)).Value

For Each Row In IndirectList1
If Application.Index(IndirectList1, Row) = Criteria1 Then
If Application.Index(IndirectList2, Row) = Criteria2 Then
If Application.Index(IndirectList3, Row) = Criteria3 Then
COUNTIFS_3CONDITIONS = COUNTIFS_3CONDITIONS + 1
End If
End If
End If
Next Row

End Function

VBA to replace COUNTIFS with INDIRECT reference to Dynamic Cells via Defined Names

JBC007

New Member

Excel Facts

Similar threads

Forum statistics

Share this page

VBA to replace COUNTIFS with INDIRECT reference to Dynamic Cells via Defined Names

JBC007

New Member

Excel Facts

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock