Excel VBA - Runtime Error '13': Incompatible Types

Question

I’m getting Error '13' Incompatible Types on the First If Line:

Count = 0
For x = 1 To LastRow Step 1
    If Cells(x, 8).Value = "Materiais" Or Cells(x, 8).Value = "Imobilizado" Then
        If Cells(x, 11).Value = "NÃO CATALOGO" Then
            Count = Count + 1
        End If
    End If
Next x

Would anyone know where the problem is?

Answer 1

Put . Text in place of . Value in if tests.

Count = 0
For x = 1 To LastRow
    If Cells(x, 8).Text = "Materiais" Or Cells(x, 8).Text = "Imobilizado" Then
        If Cells(x, 11).Text= "NÃO CATALOGO" Then Count = Count + 1
    End If
Next x

Another thing is that this code depends on being with the desired active sheet. If you don’t want it to be active, use a variable to reference it. Ex.

    set ws = ThisWorkbook.Sheets("SheetName")

    Count = 0
    For x = 1 To LastRow
        If ws.Cells(x, 8).Text = "Materiais" Or ws.Cells(x, 8).Text = "Imobilizado" Then
            If ws.Cells(x, 11).Text= "NÃO CATALOGO" Then Count = Count + 1
        End If
    Next x

Answer 2

I did tests with your code and nothing abnormal, follow what I did. Apparently it’s working properly. Already tried to revise the logic, what expected result?

Entrances

Code

Sub tst()
    LastRow = 18
    Count = 0
    For x = 1 To LastRow Step 1
        If Cells(x, 8).Value = "Materiais" Or Cells(x, 8).Value = "Imobilizado" Then
            Debug.Print Cells(x, 8).Value & vbTab & " - " & vbTab & Count
            If Cells(x, 11).Value = "NÃO CATALOGO" Then
                Count = Count + 1
            End If
        End If
    Next x
End Sub

Results:

Materiais    -  0
Materiais    -  0
Imobilizado  -  0
Materiais    -  0
Materiais    -  1
Materiais    -  1
Imobilizado  -  1
Materiais    -  1
Materiais    -  2
Imobilizado  -  2
Materiais    -  2
Materiais    -  3
Imobilizado  -  3
Imobilizado  -  3
Imobilizado  -  3
Imobilizado  -  3
Imobilizado  -  4
Imobilizado  -  4

Answer 3

Hello, I have a very limited knowledge of VBA, but I believe the problem is incompatibility between variables. I had a similar problem and to solve it I just had to remove the quotes from the variables in If. I’m sorry if I’m talking nonsense, it’s because I don’t know, hug.

Answer 4

Friend I’ll give an example that happened to me. I also had this same problem when writing a code. I had a string that contained a list of materials and used the split function to, from that string, create several substrings and play them in a vector.

                material = DAD_CP.Cells(cont, 8).Value 'Pega a lista_material de materiais e joga na string material
            If (material <> "") Then

                lista_material = Split(material, Chr(10)) 'Coloca todos os materiais no Array lista_material
                'lista_material(UBound(lista_material) + 1) = Mid(material, (InStr(InStr(1, Chr(10), material) + 1, Chr(10), material) + 1), Len(material)) ' Pega o último item da lista_material de materiais
            End If

I had declared list material and work list in the same line, and always gave this Incompatible Types error. What gave me anger is that the types were compatible yes because the material variable is a string and the variable lista_material() was a vector of the type string. But then I decided to display the variable window while I Purged the code and ended up discovering that disgraceful was not as a string vector but rather a vector type Variant, even if the code was declared as String. Can you believe it? Then I declared her String only in a separate and isolated line, and guess what? It worked! Conclusion: It seems that VBA does not recognize when vc declares two vectors of the Type string in the same line because lista_obra was as string vector but material list not.

See above that when I declare the two vectors on the same line one stays as a string and the other stays as Variant. But now see that when I declare both separately it stays with its original types. I think this must be some VBA Excel bug. But to conclude the reasoning, when this annoying error of incompatible types occurs try to declare the variables separately.