CODEWITHSUNDEEP
vbaexcel
Bobby
Bobby

Reputation: 13

Multiple VBA If Or statements

I have 3 If statements that always result in true no matter the value of "dept".
If I remove two of the If statements leaving just one it works. What am I doing wrong?

Dim Dept As String
Dim wb As Workbook

Set wb = ThisWorkbook


Dept = Application.WorksheetFunction.VLookup(Name, Sheet1.Range("F10"), 1)

If Dept = "Dept1" Or "Dept2" Then
wb.SendMail "email address", Subject:="Subject heading 1"

End If

If Dept = "Dept3" Or "Dept4" Then
wb.SendMail "email address", Subject:="Subject heading 2"

End If

If Dept = "Dept5" Or "Dept6" Then
wb.SendMail "email address", Subject:="Subject heading 3"

End If

Upvotes: 1

Views: 164

Answers (3)

Drad
Drad

Reputation: 106

Did you try adding 

If Dept = "Dept1" Or Dept = "Dept2" Then

Probably since the Or is Dept, it is always true. All IF condition get executed

Upvotes: 0

DrMarbuse
DrMarbuse

Reputation: 870

I assume Dept is a String variable.

Then the code should look like:

If Dept = "Dept1" Or Dept = "Dept2" Then
   wb.SendMail "email address", Subject:="Subject heading 1"
End If

VBA interprtates Your boolean Condition as (Dept = "Dept1") or CBool("Dept2") VBA propably has a hard time converting Your String to a boolean.

Upvotes: 0

Rory
Rory

Reputation: 34035

You can't use Or like that here. You have to repeat the variable:

If Dept = "Dept1" Or Dept = "Dept2" Then
wb.SendMail "email address", Subject:="Subject heading 1"

End If

If Dept = "Dept3" Or Dept = "Dept4" Then
wb.SendMail "email address", Subject:="Subject heading 2"

End If

If Dept = "Dept5" Or Dept = "Dept6" Then
wb.SendMail "email address", Subject:="Subject heading 3"

End If

or you could use Select Case:

Select Case Dept
  case "Dept1", "Dept2"
wb.SendMail "email address", Subject:="Subject heading 1"
case "Dept3", "Dept4"
wb.SendMail "email address", Subject:="Subject heading 2"
case "Dept5", "Dept6"
wb.SendMail "email address", Subject:="Subject heading 3"

End Select

Upvotes: 2

Related Questions