CODEWITHSUNDEEP
excelvba
user10451617
user10451617

Reputation: 37

An if statement to run based on various amounts of criteria being greater than 0

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

Upvotes: 0

Views: 81

Answers (3)

QHarr
QHarr

Reputation: 84465

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

Upvotes: 1

Brian M Stafford
Brian M Stafford

Reputation: 8868

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

Upvotes: 1

cybernetic.nomad
cybernetic.nomad

Reputation: 6408

(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

Upvotes: 1

Related Questions