Optional Variables

[Guest]

Hello - I have a UDF that counts the number of values in a range matching a
criteria. It works perfectly when I enter inputs for all variables, however
when I only enter values for condR1 and cond1 I get a #VALUE!. I think it's
because condR2 and cond2 are now null so when it checks for "cond2Arr(i, 1) =
cond2" it fails. How can I get by this? Is there a check I can do to see if
condR2 is null or something? Thanks.

Function TempCount(condR1 As Range, cond1 As String, Optional condR2 As
Range, Optional cond2 As String)
Dim cond1Arr, cond2Arr
cond1Arr = condR1
cond2Arr = condR2

For i = 1 To UBound(cond1Arr)
If (cond1Arr(i, 1) = cond1 And cond2Arr(i, 1) = cond2) Then
temp = temp + 1
End If
Next i
TempCount = temp
End Function

 

[Chip Pearson]

Dave,

The variable condR2 will be Nothing if it is omitted, so you can
test for that. E.g.,

If condR2 Is Nothing Then
' omitted
End If



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com