If condition#1 AND Application.Match Then

Question

I want to check for two conditions:

1. If Tabelle3.Cells(7 + i, 1) <> ""
2. If the Tabelle3.Cells(7 + i, 1) can be found in Tabelle8.Range("A:A")

In case one of them is not fulfilled I want it to jump to the next i.

Therefore, I'm using Application.Match for the second condition and the code is the following:

If Tabelle3.Cells(7 + i, 1) <> "" And Application.Match(Tabelle3.Cells(7 + i, 1), Tabelle8.Range("A:A"), False) Then

But the Run-Time Error '13' "Types Incompatible" occurs. Does someone know why and how I can make this one work? :)

Below the whole code:

Sub Test()

    Dim lastrow2 As Long
    lastrow2 = Tabelle3.Range("A" & Rows.Count).End(xlUp).Row
    Set myrange2 = Tabelle8.UsedRange

    For i = 2 To lastrow2
        If Tabelle3.Cells(7 + i, 1) <> "" And Application.Match(Tabelle3.Cells(7 + i, 1), Tabelle8.Range("A:A"), False) Then
            Tabelle3.Cells(7 + i, 19) = Application.WorksheetFunction.VLookup(Tabelle3.Cells(7 + i, 1), myrange2, 3, False)
            Tabelle3.Cells(7 + i, 20) = Application.WorksheetFunction.VLookup(Tabelle3.Cells(7 + i, 1), myrange2, 4, False)
        End If
    Next i

End Sub

Answer 1

Run this code:

Sub TestMe()        
    Debug.Print CBool(Application.Match("Something", Range("A:A"), False))    
End Sub

It prints True on the immediate window, although there is no string "Something" on the first column of your worksheet. Thus in your case, Application.Match(Tabelle3.Cells(7 + i, 1), Tabelle8.Range("A:A"), False) will always be evaluated to True and this is not how it should be.

Consider some check for errors like IsError(Application.Match(Tabelle3.Cells(7 + i, 1), Tabelle8.Range("A:A"), False)), which would be True, in case that the value cannot be found.