Excel VBA runtime error 13

[pedrofilipegomes]

I have runtime error 13 in my file.

The error is in this code:
"
Dim i, k As Long
i = 0
k = 0
While Cells(i + 1, 1) <> ""
i = i + 1
If i = 1 Then
k = k + 1
Sheets("Final").Cells(k, 1) = Cells(i, 1)
Sheets("Final").Cells(k, 2) = Cells(i, 2)
Sheets("Final").Cells(k, 3) = Cells(i, 3)
Sheets("Final").Cells(k, 4) = Cells(i, 4)
Sheets("Final").Cells(k, 5) = Cells(i, 5)
Sheets("Final").Cells(k, 6) = Cells(i, 6)
Sheets("Final").Cells(k, 7) = Cells(i, 7)
Sheets("Final").Cells(k, 8) = Cells(i, 8)
Sheets("Final").Cells(k, 9) = Cells(i, 9)
ElseIf Cells(i, 11) <> "Não" And (Cells(i, 12) = "FUTURA" Or Cells(i, 12) = "CORRENTE" Or Cells(i, 12) = "PERMANENTE") Then
k = k + 1
Sheets("Final").Cells(k, 1) = Cells(i, 1)
Sheets("Final").Cells(k, 2) = Cells(i, 2)
Sheets("Final").Cells(k, 3) = Cells(i, 3)
Sheets("Final").Cells(k, 4) = Cells(i, 4)
Sheets("Final").Cells(k, 5) = Cells(i, 5)
Sheets("Final").Cells(k, 6) = Cells(i, 6)
If Sheets("Extract").Cells(i, 13) = "" Then
Else
Sheets("Final").Cells(k, 7) = Cells(i, 13)
End If
Sheets("Final").Cells(k, 8) = Cells(i, 8)
Sheets("Final").Cells(k, 9) = Cells(i, 9)
End If
Wend
"
The line where i have error is marked with red bold.

Anyone can help me?

Thank's

 

[baitmaster]

what are the values of i, cells(i,11) and cells(i,12)?

 

[pedrofilipegomes]

The values of i are text.

Can be anyone problem with the data type?

Thank's

 

[baitmaster]

No, please provide more details

When you run the code and it breaks, you are able to enter Debug mode. Do this and the row highlighted will be shown in yellow. At this point, what is the actual value of i? And what is the actual value of each cell in question?

To find these out, you can move the mouse over certain variables in your code. You can also use the Immediate window (Ctrl & G to show it) and ask questions by typing ? followed by a line of code, for example:
?i
?Cells(i, 11).value

 

[pedrofilipegomes]

When i run the code and it breaks, the value of i is 2, Cells(i,11)="Não" and the value of Cells(i,12)= error 2042.

Can you help me please?

 

[baitmaster]

Yes, the formula in cells(i,12) is currently returning a #N/A error. You need to either (1) fix the formula so that it doesn't do this, or (2) fix your code so that it knows what to do when trying to process an error value. Why is the formula returning an error?

 

[pedrofilipegomes]

I don't know why the formula is giving an error. Doesn't always give an error when i run the macro. You can help me to change the formula in order to solve the error?

Thank's.

 

[baitmaster]

Well yes but you have to give me a little more information...

You need to find out what the reason for the error is, or add a default value that simply overrides it. I wouldn't recommend simply hiding an error without understanding it, but you could use e.g. "=IFERROR(your formula,"error")

Otherwise, when your code breaks, you need to trace the N/A error back to it's source. What is the actual formula in that cell?

 

[pedrofilipegomes]

Ok i understand and thank's for all.

The formula is INDEX(Pivot!$A:$A;MATCH(VALUE(LEFT(B2;3));Pivot!$C:$C;0))

 

[baitmaster]

Then I suspect one of the following:
- B2 contains #N/A
- B2 (or at least, the string in the first 3 characters) does not exist in your list at column C
- Column A contains #N/A