CODEWITHSUNDEEP
excelvba
orange123
orange123

Reputation: 75

How to create a if statement with multiple OR conditions?

Basically in my if statement I need to firstly see if the string is 7 characters and if the 7th character in that cell is a character among "A, J, S, T, N, V".

This is what I have so far (I know it doesn't work, but I am not sure how to implement these multiple "ORs"

If Len(Cells(i, 7).Value) = 7 And Left(Cells(i, 7), 1) = "A" Or "J" Or "S" Or "T" Or "N" Or "V" Then
                    Cells(i, 29).Value = "Client and Account fields switched"
Else
Else If

Thank you in advance! I am quite new haha

Upvotes: 2

Views: 588

Answers (3)

VBasic2008
VBasic2008

Reputation: 55073

Application.Match

  • Note, that this option is not case-sensitive i.e. A=a.

The Code

Option Explicit

Sub testMatch()
    If Len(Cells(i, 7).Value) = 7 Then
        If IsNumeric(Application.Match(Right(Cells(i, 7), 1), _
                Array("A", "J", "S", "T", "N", "V"), 0)) Then
            Cells(i, 29).Value = "Client and Account fields switched"
        Else
        End If
    Else
    End If
End Sub

By implementing variables the previous could become more readable:

Sub testMatchReadable()
    Const LettersList As String = "A,J,S,T,N,V"

    Dim Letters() As String: Letters = Split(LettersList, ",")
    Dim cValue As Variant: cValue = Cells(i, 7).Value

    Dim cMatch As Variant

    If Not IsError(cValue) Then
        If Len(cValue) = 7 Then
            cMatch = Application.Match(Right(cValue, 1), Letters, 0)
            If IsNumeric(cMatch) Then
                Cells(i, 29).Value = "Client and Account fields switched"
            Else
            End If
        Else
        End If
    End If

End Sub

Upvotes: 0

Gustav
Gustav

Reputation: 56026

First, you ask for the 7th character, thus use Right.

Then, use InStr for tight code:

If Len(Cells(i, 7).Value) = 7 And InStr(1, "AJSTNV", Right(Cells(i, 7), 1), vbtextcompare) > 0 Then
    Cells(i, 29).Value = "Client and Account fields switched"
Else
Else If

Or, which I would prefer, the simple and maintainable method:

If Len(Cells(i, 7).Value) = 7 Then
    Select Case Right(Cells(i, 7), 1)
        Case "A", "J", "S", "T", "N", "V"
            Cells(i, 29).Value = "Client and Account fields switched"
    End Select
End If

Upvotes: 2

Scott Craner
Scott Craner

Reputation: 152660

you will need to test each one:

If Len(Cells(i, 7).Value) = 7 And _
    (Left(Cells(i, 7), 1) = "A" Or _
     Left(Cells(i, 7), 1) = "J" Or _
     Left(Cells(i, 7), 1) =  "S" Or _
     Left(Cells(i, 7), 1) =  "T" Or _
     Left(Cells(i, 7), 1) =  "N" Or _
     Left(Cells(i, 7), 1) =  "V") Then
                Cells(i, 29).Value = "Client and Account fields switched"
Else If

Upvotes: 4

Related Questions