Error: Incompatible Types

Question

Error: Incompatible Types

Navigation

#1 by (3 votes)

1

I'm running a PROCV on one column, looking for values from another. The code runs up to a certain value and the error quoted appears. But there is no difference in the numbers, I have already put all values as the first one to test and continue giving the same error (always in the 8th row of the column).

follow the code

i = 2

Do Until Range("F" & i) = ""

    Range("H" & i).Select
        ActiveCell.FormulaR1C1 = "=IF(VLOOKUP(R[0]C[-2],C[-5],1,FALSE), ""OK"")"

        If Cells(8, i).Value = "OK" Then    'Aqui aparece o erro
            Cells(8, i).Copy
            Sheets("Teste").Select
            Cells(j, 2).Select
            Sheets("Planilha4").Select

        j = j + 1
        End If
  i = i + 1
Loop

vba excel-vba

asked by anonymous 12.06.2018 / 18:20

1 answer

Bootstrap - Paint a whole line and less a specific column Show photo of a listbox item gives me error Out Of Memory

score 3 · Accepted Answer

Leandro,

In addition to the inversion as your colleague above pointed out, you have another problem with your code. When PROCV does not find the searched value, it returns a # N / D and this is causing the type mismatch error.

To solve, just include a IFERROR ( SEERRO in Excel in pt) and indicate which value you want to appear in case of an error. I tested the code below and it worked perfectly here, notice that in case of error, it will appear "NOT LOCATED".

i = 2
j = 2

Do Until Range("F" & i) = ""

    Range("H" & i).Select
        ActiveCell.FormulaR1C1 = "=IFERROR(IF(VLOOKUP(R[0]C[-2],C[-5],1,FALSE)=R[0]C[-2], ""OK""),""NÃO LOCALIZADO"")"

        a = Cells(i, 8).Value

        If Cells(i, 8).Value = "OK" Then
            Cells(i, 8).Copy
            Sheets("Teste").Select
            Cells(j, 2).Select
            Sheets("Planilha4").Select

        j = j + 1
        End If
  i = i + 1
Loop