Error 13, Type Mismatch...Why????

[Desu Nota from Columbus]

For i = 2 To 10000
    If Cells(i, 6).Value = 0 Then    `error on this line
        Cells(i, 6).Delete Shift:=xlUp
    End If
Next i

I tried using

Range("F" & i).Value = 0 Then

and it didn't work either.

What am I not understanding?

 

[Scott Huish]

What have you dimmed i as?

BTW, that is not going to work, you will have to work backwards through the range with a Step -1
For just one line of code, I would personally use a single-line If instead of the Block If.

 

[Scott Huish]

Sub test()
Dim i As Long
Application.ScreenUpdating = False
    For i = 10000 To 2 Step -1
        If Cells(i, 6) = 0 Then Cells(i, 6).Delete
    Next i
Application.ScreenUpdating = True
End Sub

 

[mikerickson]

Is it possible that Cells(i,6) contains an error value (e.g. #DIV/0)? That would cause a type mismatch (= doesn't handle errors).

If that's the case this would be one way to fix it.

If CStr(Cells(i, 6).Value) = "0" Then

 

[Desu Nota from Columbus]

i was defined as an Integer,

yes it was possible that there was an error, and i corrected this by changing a formula further up my in my code (now there are only text and 0's possible in the range)

I will post back once i check

 

[Desu Nota from Columbus]

Thanks to you both!

I forgot I needed to step back through the range.

Why does i (changed to r) have to be defined as a long? Isn't the range Cells(r, 26) dealing with integers small enough to cover what i need?

An integer is defined to be accepted up to 2^32 (aka much less than 1000)...correct?

 

[Scott Huish]

It doesn't have to be, but there is no point in using the Integer data type because VBA is going to convert it to a Long integer anyway.

See here: http://msdn.microsoft.com/en-us/library/aa164754(v=office.10).aspx

 

[Desu Nota from Columbus]

It doesn't have to be, but there is no point in using the Integer data type because VBA is going to convert it to a Long integer anyway.

See here: http://msdn.microsoft.com/en-us/library/aa164754(v=office.10).aspx

Thanks for the clarification!