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 |
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 |
All times are GMT +1. The time now is 01:13 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com