CODEWITHSUNDEEP
vbatypesruntimemismatch
RG1of2
RG1of2

Reputation: 53

VBA Run time error type mismatch

I am getting the following msg from compiler

"Run-time error '13': Type mismatch"

and it's highlighting the following line:

If Cells(k, j + 1) = "T1" ...

I don't understand why I am getting this error. The cells that it is looping through are all text format. Can someone explain how to correct this code? Note that this is only a portion of the program.

Sub TestSub2()

Dim i As Integer, j As Integer, k As Integer, k2 As Integer


'i ->tier table row counter
'j ->column counter
'k ->"Reading Date" and "PM Executed?" table row counter

'---TIER 1 TABLE---
k = 102
k2 = 84


For i = 21 To 35

    k = k + 1
    k2 = k2 + 1

    For j = 26 To 148
        If Cells(k, j + 1) = "T1" Or "T1, T2" Or "T2, T1" Or "T1, T3" Or "T3, T1" Or "T1, T2, T3" Or "T1, T3, T2" Or "T2, T1, T3" Or "T2, T3, T1" Or "T3, T1, T2" Or "T3, T2, T1" Then
    'then
        Cells(i, 8) = Cells(i, j) And Cells(i, 9) = Cells(k2, j + 1)
    End If

    Next j

Next i

Upvotes: 0

Views: 86

Answers (2)

SierraOscar
SierraOscar

Reputation: 17647

Or and And are Logical Operators in and are therefore used to compare two or more expressions and return a Boolean value.

If you want to execute two statements on the same line, you can do so by using a colon : like so:

Cells(i, 8).Value = Cells(i, j).Value: Cells(i, 9).Value = Cells(k2, j + 1).Value

However this generally reduces the readbility of your code so isn't widely used in .

There are other ways of using boolean comparisons too, for example Select Case or the less used Like operator for limited pattern matching. For example:

If Cells(k, j + 1).Value Like "T[1-3]((, T[1-3])+)?" Then
    '// Do something
Else
    '// Do something Else
End If

Or perhaps a Select Case block:

Select Case Cells(k, j + 1).Value
    Case "T1", "T1, T2", "T2, T1", "T1, T3", "T3, T1" '// etc...
        '// Do Something
        '// You could include further tests such as...
    Case "Test 1", "Test Another"
        '// Also do something...
        '// Or code for the chance that none of the conditions are met:
    Case Else
        '// Nothing matched - do something else.
End Select

Your original code however, would need to look more like this:

If Cells(k, j + 1).Value = "T1" Or Cells(k, j + 1).Value = "T1, T2" Or _
    Cells(k, j + 1).Value = "T2, T1" Or Cells(k, j + 1).Value = "T1, T3" Or _
    Cells(k, j + 1).Value = "T3, T1" Or Cells(k, j + 1).Value = "T1, T2, T3" Or _
    Cells(k, j + 1).Value = "T1, T3, T2" Or Cells(k, j + 1).Value = "T2, T1, T3" Or _
    Cells(k, j + 1).Value = "T2, T3, T1" Or Cells(k, j + 1).Value = "T3, T1, T2" Or _
    Cells(k, j + 1).Value = "T3, T2, T1" Then

        Cells(i, 8).Value = Cells(i, j).Value
        Cells(i, 9).Value = Cells(k2, j + 1).Value

End If

As you can see this becomes rather laborious and so I would suggest one of the other methods to do your comparison.

Upvotes: 1

Chaz
Chaz

Reputation: 339

The "Or" operator needs to separate the boolean expressions, not the values on the right hand side of the equality. Change it like this:

val = Cells(k, j + 1)
If   val = "T1" Or val = "T1, T2" Or val = "T2, T1" Or val = "T1, T3" Or val = "T3, T1" Or val = "T1, T2, T3" Or val = "T1, T3, T2" Or val = "T2, T1, T3" Or val = "T2, T3, T1" Or val = "T3, T1, T2" Or val = "T3, T2, T1" Then

Upvotes: 0

Related Questions