CODEWITHSUNDEEP
vbaexcel
sc1324
sc1324

Reputation: 600

Type mismatch runtime error (13) for vba

I want to do filter for the region and delete the rest of the rows that don't match the region. The sheet has no formulas in it only values and characters. This is part of the larger codes I am working on so I am only going to post this part and this is the first time I have seen the error so for other worksheets they work just fine the way I declared.

The line that has an error and won't pass through is here If InStr(1, Rng10.Cells(q, 1).Value, "NW") = 0 Then

My data has a helper column which is W and I am filtering it. I made sure there is no duplicate for the variables I used. (I used, s, t, m, n and etc...) I tried to declare q as double or variate and neither of them don't work.

sub test()
Worksheets("A").Activate
    'filter
    Dim sh9 As Worksheet
    Set sh9 = Sheets("A")
    Dim LR16 As Long
    Dim Rng10 As Range
    Dim q As Long
    LR16 = sh9.Cells(Rows.Count, "B").End(xlUp).Row
    Set Rng10 = Range("W5:W" & LR16 - 1)
    For q = Rng10.Rows.Count To 1 Step -1
    If InStr(1, Rng10.Cells(q, 1).Value, "NW") = 0 Then
    Rng10.Cells(q, 1).EntireRow.Delete
    End If
    Next q
end sub

Upvotes: 0

Views: 988

Answers (2)

Mathieu Guindon
Mathieu Guindon

Reputation: 71207

If InStr(1, Rng10.Cells(q, 1).Value, "NW") = 0 Then

You're assuming what the type of Rng10.Cells(q, 1).Value is going to be, and you're assuming that whatever that type is, VBA can implicitly convert it to a String to pass to the InStr function.

When a cell contains an error value (#N/A, #VALUE!, #REF! or any other error), then the type of thatCell.Value is Error - and VBA does NOT know how to convert Error values to String values (or anything else for that matter), so it raises run-time error 13 type mismatch and forces you to fix your code instead.

You can check if a cell's value is an Error using the IsError function:

Dim myValue As Variant
myValue = Rng10.Cells(q, 1).Value
If IsError(myValue) Then
    'cell contains an error
Else
    'cell contains no error: myValue is safe to convert to a string
    If InStr(1, CStr(myValue), "NW") = 0 Then
        '...
    End If
End If

Side note, notice how proper indentation makes the code easier to follow.

Upvotes: 3

CLR
CLR

Reputation: 12279

Don't use .Value, use .Text to see errors as text equivalent.

Change

If InStr(1, Rng10.Cells(q, 1).Value, "NW") = 0 Then

To

If InStr(1, Rng10.Cells(q, 1).Text, "NW") = 0 Then

Upvotes: 2

Related Questions