Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Optional Variables
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Optional Variables
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 "Dave" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
optional calculations | Excel Worksheet Functions | |||
Optional hyphen? | Excel Discussion (Misc queries) | |||
optional parameter | Excel Programming | |||
optional args in UDF | Excel Programming | |||
Optional Linking | Excel Discussion (Misc queries) |