CODEWITHSUNDEEP
vbaexcel
Armitage2k
Armitage2k

Reputation: 1254

excel vba if statement with OR and AND scenario

I have an email messaging system that chooses a different language template based on the nationality of the current record. I need to make the language selection dynamic though, since the end user does not always want to send the message in the entry's national language, which means that for example if the record state a nationality of "IT" (Italy), I want to check if the languages template is set as "Active" before proceeding, respectively default to EN (English) in case the template is "Inactive".

'check for nationality and select letter template accordingly
    ' Use german for DE, AT and CH nationalities
    If nationality = "DE" Or nationality = "AT" Or nationality = "CH" Then
        Set rng = Sheets("PostStayEmail_DE").Range("A1:B30").SpecialCells(xlCellTypeVisible)
        With ActiveWorkbook.Sheets("PostStayEmail_DE")
            'do something
        End With`

    'Use italian for IT nationalities
        ElseIf nationality = "IT" Then
        Set rng = Sheets("PostStayEmail_IT").Range("A1:B30").SpecialCells(xlCellTypeVisible)
        With ActiveWorkbook.Sheets("PostStayEmail_IT")
              'do something

        End With

Now, what I need for the if statement would be something like this

if (nationality = "DE" or nationality = "AT" or nationality = "CH") _
                          AND (Range("B5") = "Active") then
    'do something

Elseif ... 'check other languages

How can I combine those OR statements for multiple languages but also add an AND statement to check if the template is active?

Upvotes: 2

Views: 157

Answers (3)

flohdieter
flohdieter

Reputation: 130

If (nationality = "DE" OR nationality = "AT" OR nationality = "CH") _
                          AND Range("B5") = "Active" then

'do something

End If

This should work

Upvotes: 0

user4039065
user4039065

Reputation:

A Select Case statement may help tidy up the multiple matches.

if lcase(Range("B5").Value) = "active" then
    select case ucase(nationality)
        case "DT", "AT", "CH"
            With ActiveWorkbook.Sheets("PostStayEmail_DE")
                Set rng = .Range("A1:B30").SpecialCells(xlCellTypeVisible)
                'do something
            End With
        case "IT"
            With ActiveWorkbook.Sheets("PostStayEmail_IT")
                Set rng = .Range("A1:B30").SpecialCells(xlCellTypeVisible)
                'do something
            End With
        case else
            'do thing if no matches
    end select
else
    'do stuff when B5 is not 'Active'
end if

Depending upon the repetition of the code for the various combinations of ISO 3166-1 alpha-2 two letter country codes, you may simply wish to record the worksheet name and leave all further processing in a single block below the Select Case.

Upvotes: 1

Karthick Gunasekaran
Karthick Gunasekaran

Reputation: 2713

If ((nationality = "DE" Or nationality = "AT" Or nationality = "CH") And Range("B5").Value = "Active") Then
    Set Rng = Sheets("PostStayEmail_DE").Range("A1:B30").SpecialCells(xlCellTypeVisible)
    With ActiveWorkbook.Sheets("PostStayEmail_DE")
        'do something
    End With
ElseIf ((nationality = "IT") And Range("B5").Value = "Active") Then
    Set Rng =   Sheets("PostStayEmail_IT").Range("A1:B30").SpecialCells(xlCellTypeVisible)
    With ActiveWorkbook.Sheets("PostStayEmail_IT")
        'do something
    End With
End If

Upvotes: 1

Related Questions