Multiple VBA If Or statements

Question

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

I've been programming for > 20 years and just learned something new from you with that Application.WorksheetFunction.VLookup(.... Didn't know the built in formula functions were available like that. Thanks! — DarrenMB
– DarrenMB, Commented Sep 25, 2014 at 15:08

Rory · Accepted Answer · 2014-09-25 14:19:02Z

2

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

answered Sep 25, 2014 at 14:19

Rory

34.2k5 gold badges40 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

DrMarbuse · Accepted Answer · 2014-09-25 14:21:41Z

0

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.

answered Sep 25, 2014 at 14:21

DrMarbuse

88011 silver badges30 bronze badges

Comments

Drad · Accepted Answer · 2014-09-25 14:22:14Z

0

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

answered Sep 25, 2014 at 14:22

Drad

1061 silver badge3 bronze badges

Collectives™ on Stack Overflow

Multiple VBA If Or statements

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related