Dfyjkt

I'm writing an if statement with 5 separate criteria based on whether they are greater than zero.

I want 6 separate if statements based on whether 0 criteria >0, 1 criteria > 0 , 2 etc... up to 5 criteria > 0. I was trying to do this with OR and AND statements, but it's obviously not the solution.

What I'm essentially after:

If 0 Criteria > 0 Then
 Do this
ElseIf 1 Criteria > 0 Then
 Do this
ElseIf 2 Criteria > 0 Then
 Do this
....
ElseIf 5 Criteria > 0 Then
 Do this
End If

(this is untested)

Dim c as long

c = 0

If criteria1 = 0 then CritNo = CritNo + 1
If criteria2 = 0 then CritNo = CritNo + 1
If criteria3 = 0 then CritNo = CritNo + 1
If criteria4 = 0 then CritNo = CritNo + 1
If criteria5 = 0 then CritNo = CritNo + 1
If criteria6 = 0 then CritNo = CritNo + 1

Select case CritNo 
 Case 1
 Do stuff
 Case 2
 Do other stuff
 ...
 Case Else
 Do nothing
End select

Here's another idea:

Select Case Abs(criteria1 > 0) + Abs(criteria2 > 0) + Abs(criteria3 > 0) + Abs(criteria4 > 0) + Abs(criteria5 > 0)
 Case 0
 MsgBox "0 criteria"
 Case 1
 MsgBox "1 criteria"
 Case 2
 MsgBox "2 criteria"
 Case 3
 MsgBox "3 criteria"
 Case 4
 MsgBox "4 criteria"
 Case 5
 MsgBox "5 criteria"
End Select

You could use evaluate on an array of the criteria with SUMPRODUCT

Option Explicit
Public Sub test()
 Dim criteria1 As Long, criteria2 As Long,criteria3 As Long
 Dim criteria4 As Long, criteria5 As Long, arr()
 Dim numGreaterThanZero As Long

 criteria1 = -1
 criteria2 = 3
 criteria3 = 0
 criteria4 = 5
 criteria5 = 6

 arr = Array(criteria1, criteria2, criteria3, criteria4, criteria5)

 numGreaterThanZero = Application.Evaluate("=SUMPRODUCT(--(" & Join$(arr, ";") & ">0))")

 Select Case numGreaterThanZero

 Case 1
 Case 2
 Case 3
 Case 4
 Case 5

 End Select
End Sub

If you actually just want to test the conditions separately (and not a cumulative count) and exit at first True then:

Select Case True

Criteria1 > 0
'Do Something

Criteria2> 0
'Do something else

End Select