# Count Uniqe With Criteria UDF (Addin Question)

##### Board Regular
Good Morning/Afternoon!

I created a user Defined Function that works amazing whenit is in the active workbooks module. However, when I tried to turn it into an add-init gives me a #Value Error. I know why it is doing this, I just cant figureout how to solve it. I need a way to use the UDF variables to look at the activeworkbook for the ranges. Currently it is trying to use the range of the add-inand not the workbook that I am calling it from. It also doesnt work with themultiple criteria if I am using ranges on different sheets in the same workbook.

This function will count all unique values in a range with an optional 4 criteria.

Here is the code:

Code:
``````Public Function COUNTUNIQUE( _
rng As Range, _
Optional LookupRange_1 As Range, Optional Criteria_1 As Variant, _
Optional LookupRange_2 As Range, Optional Criteria_2 As Variant, _
Optional LookupRange_3 As Range, Optional Criteria_3 As Variant, _
Optional LookupRange_4 As Range, Optional Criteria_4 As Variant _
) As Integer

'Declare Supporting Variables
Dim dict As Dictionary
Dim cell As Range

'Ensure Dictionary is empty
Set dict = New Dictionary
'Loop through each cell to check criteria, and add to dictionary
For Each cell In rng.Cells

'Check if Criteria One is met in Criteria One Range, If not goes to NextVal label
If IsMissing(Criteria_1) = False Then
If ActiveWorkbook.ActiveSheet.Cells(cell.Row, LookupRange_1.Column) = Criteria_1 Then
Else
GoTo NextVal:
End If
End If

'Check if Criteria Two is met in Criteria Two Range, If not goes to NextVal label
If IsMissing(Criteria_2) = False Then
If Cells(cell.Row, LookupRange_2.Column).Value = Criteria_2 Then
Else
GoTo NextVal:
End If
End If

'Check if Criteria Three is met in Criteria Three Range, If not goes to NextVal label
If IsMissing(Criteria_3) = False Then
If Cells(cell.Row, LookupRange_3.Column).Value = Criteria_3 Then
Else
GoTo NextVal:
End If
End If

'Check if Criteria Four is met in Criteria Four Range, If not goes to NextVal label
If IsMissing(Criteria_4) = False Then
If Cells(cell.Row, LookupRange_4.Column).Value = Criteria_4 Then
Else
GoTo NextVal:
End If
End If

If Not dict.Exists(cell.Value) Then
End If

NextVal:

Next

'Counts objects in dictionay and sets the value to the Functions Return Value
COUNTUNIQUE = dict.Count

End Function``````

Any Ideas?

### Excel Facts

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

##### Board Regular
Edit,

"ActiveWorkbook.ActiveSheet." In Criteria_1 Was just a test.

Funny thing is if i close the workbook and reload it, it calculates correctly. But, if i edit the formula it gives me #Value .

##### Board Regular
So i have identified the issue. Every time i edit the formula, excel looses the link to the Add-in file. Anyone know of a way to correct this?

Replies
2
Views
3K
Replies
8
Views
213
Replies
1
Views
90
Replies
3
Views
317
Replies
0
Views
217