Error: The Incompatible Types

Asked

Viewed 526 times

1

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

follows 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
  • 2

    Behold documentation of the Range.Cells property, where the syntax is: .Cells(Linha,Coluna). So since you want the H column, try reversing 8 and i. So it would be: If Cells(i, 8).Value = "OK" Then

  • It had already reversed, it did not solve the problem. The error in the first row of the table instead of the 8.

1 answer

3


Leandro,

In addition to the inversion as the colleague above signaled, there is another problem in your code. When the PROCV does not locate the searched value, it returns an N/D and this is causing the type incompatibility error.

To solve just include a IFERROR (SEERRO in Excel en) and indicate which value you want to appear in case of error. I tested the code below and it worked perfectly here, note 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

  • Junior, with this change, the error "application definition or object definition error" appears in the line of 'Cells(j, 2)...'. You know why?

  • 2

    @Leandrolazari Probably because the value of j is equal to 0. Just below i=2, try to do j=2

  • It worked Junior! Thank you so much for your help!

  • Thank you @danieltakeshi.

  • @Leandrolazari, I believe there’s a problem with the PROCV formula. I believe the correct is: =IFERROR(IF(VLOOKUP(R[0]C[-2],C[-5],1,FALSE)=R[0]C[-2], ""OK"),"""NOT LOCATED"")". Do the test there.

Browser other questions tagged vba excel-vba

You are not signed in. Login or sign up in order to post.