Run-time error '13': Type mismatch on IF c

Question

In my VBA code using excel I have

Dim Field2 As String

Field2 = Cells(i, 4).Value

If Right(Field2, 3) = ("A-1" Or "A-2" Or "B-1" Or "B-2" Or "C-1" Or "C-2" Or "D-1" Or "D-2" Or "D-3") Then
          Cells(i, 50).Value = "OtherPrtnrs /Dcrs & Dept heads"
End If

An when I run the code, I get the follow message: "Run-time error '13': Type mismatch"

Do you have any idea how to make this code work?

Thank you

Fabio

Answer 1

Try this

If Right(Field2, 3) = "A-1" Or _
   Right(Field2, 3) = "A-2" Or _
   Right(Field2, 3) = "B-1" Or _
   Right(Field2, 3) = "B-2" Or _
   Right(Field2, 3) = "C-1" Or _
   Right(Field2, 3) = "C-2" Or _
   Right(Field2, 3) = "D-1" Or _
   Right(Field2, 3) = "D-2" Or _
   Right(Field2, 3) = "D-3" Then
      Cells(i, 50).Value = "OtherPrtnrs /Dcrs & Dept heads"
End If

Or better still... this

Select Case Right(Field2, 3)
Case "A-1","A-2","B-1","B-2","C-1","C-2","D-1","D-2","D-3"
    Cells(i, 50).Value = "OtherPrtnrs /Dcrs & Dept heads"
End Select

Note: I am assuming that i is a valid row number

Explanation:

When comparing using an If statement, you cannot say If A = B or C. You are supposed to compare is separately. If A = B or A = C Then. Here each OR is its own Boolean statement i.e it will be evaluated separately.

When you have multiple such comparisons, it is better to use a Select Statement as shown in the example above.

Answer 2

Here are the alternatives:

You may use the Excel OR function as follows

Dim Str As String, ChkStr As Boolean
Field2 = Cells(i, 4)
Str = Right(Field2, 3)
ChkStr = WorksheetFunction.Or(Str = "A-1", Str = "A-2", Str = "B-1", Str = "B-2", Str = "C-1", _
                              Str = "C-2", Str = "D-1", Str = "D-2", Str = "D-3")

If ChkStr Then          'Alternatively: If ChkStr = True Then
    Cells(i, 50) = "OtherPrtnrs /Dcrs & Dept heads"
End If

or a slightly different approach from Siddharth Rout's answer using the benefit of the Select Case statement on text, especially for "D-1", "D-2", and "D-3", to determine if the condition lies between other text in an alphabetical sense. Caution: this can at times cause unexpected results so make sure you test it beforehand.

Select Case Right(Field2, 3)
    Case "A-1" To "A-2", "B-1" To "B-2", "C-1" To "C-2", "D-1" To "D-3"
         Cells(i, 50) = "OtherPrtnrs /Dcrs & Dept heads"
End Select

The last but not the least is to use For Each...Next statement of an array

StrList = Array("A-1", "A-2", "B-1", "B-2", "C-1", "C-2", "D-1", "D-2", "D-3")

For Each element In StrList
    If Right(Field2, 3) = element Then
        Cells(i, 50) = "OtherPrtnrs /Dcrs & Dept heads"
        Exit For
    End If
Next

One point that should be made here is that, by default, VBA code is case sensitive.