0
votes

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
2
I suggest adding tag(s) that relate to the programming language you are using. Furthermore, in the question body, please include the entire error message, not just its error code. Sometimes this helps.onebree
ok i have indicated that it is VBA. The error msg is type mismatch.RG1of2
Please edit the question and add tags for VBA, and also paste or even screen shot the message provided, word for wordonebree
even though pasting it verbatim added no clarity, i did it. added tag too. any clue what the issue is?RG1of2

2 Answers

1
votes

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.

0
votes

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