VBA Run time error type mismatch

Question

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

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. — user3373470
– user3373470, Commented Oct 5, 2015 at 19:11
ok i have indicated that it is VBA. The error msg is type mismatch. — RG1of2
– RG1of2, Commented Oct 5, 2015 at 19:22
Please edit the question and add tags for VBA, and also paste or even screen shot the message provided, word for word — user3373470
– user3373470, Commented Oct 5, 2015 at 19:22
even though pasting it verbatim added no clarity, i did it. added tag too. any clue what the issue is? — RG1of2
– RG1of2, Commented Oct 5, 2015 at 19:31

SierraOscar · Accepted Answer · 2015-10-05 20:31:45Z

1

Or and And are Logical Operators in vba 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 vba.

There are other ways of using boolean comparisons too, for example Select Case or the less used Like operator for limited regex 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.

edited Oct 5, 2015 at 20:31

answered Oct 5, 2015 at 20:24

SierraOscar

17.7k6 gold badges44 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

RG1of2 Over a year ago

great answer. for the select case method, you meant to type <case is = "T1" ...> right

SierraOscar Over a year ago

@RG1of2 nope, Case Is is for when you're using comparison operators in your Case statement :)

Chaz · Accepted Answer · 2015-10-05 19:29:51Z

0

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

answered Oct 5, 2015 at 19:29

Chaz

3391 silver badge6 bronze badges

Collectives™ on Stack Overflow

VBA Run time error type mismatch

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related