Type Mismatch error

Question

I have the below code that is giving me Run Time Error 13, Type Mismatch on the line "If ws1.cells(i,13)="Yes" Then" That column (Column M) contains either blank cells, or "Yes". I've tried redefining "i" as String, and it didn't change anything. Goal is that for every row with "Yes" in column M, that entire row is copied over to a second sheet named "Output". Any help with this error would be much appreciated, also open to other ideas that may suit my goal. Thanks!

  Sub Sadface()
  Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("Trades")
  Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Sheets("Output")

  For i = 2 To ws1.Range("M65536").End(xlUp).Row
  If ws1.Cells(i, 13) = "Yes" Then
  ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 2).End(xlUp).Row + 1)
  End If
  Next i

  End Sub

Try using ws1.Cells(i, 13).Value to make it perfectly clear to VBA that you want the value in that cell. — Degustaf
Thanks Degustaf, but it continues to give me the same error message! — user3761404
You have an error somewhere in Trades!M:M. Go to the Trades worksheet and select all of column M. Tap F5 then click Special. Check Formulas and uncheck everything but Errors. Click OK. If you do not find an error repeat but check Constants instead of Formulas. If an error is found, clarify whether you want to skip it or remove it. — user4039065

Unknown Unknown · Accepted Answer · 2015-06-02T20:53:03

It sounds as if you have manually removed the errors from the external data. If bringing this data into your workbook is an operation that is repeated on a regular basis, you may wish to automate it.

Sub Happyface()
    Dim i As Long
    Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("Trades")
    Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Sheets("Output")

    With ws1.Columns(13)
        On Error Resume Next
        .SpecialCells(xlCellTypeConstants, xlErrors).ClearContents
        .SpecialCells(xlCellTypeFormulas, xlErrors).ClearContents
        Err.Clear
        On Error GoTo 0
    End With

    With ws1
        For i = 2 To .Cells(Rows.Count, "M").End(xlUp).Row
            If .Cells(i, 13) = "Yes" Then
                .Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 2).End(xlUp).Row + 1)
            End If
        Next i
    End With

End Sub

As much as I try to avoid the use of On Error Resume Next¹, this is the most expedient method of dealing with the Range.SpecialCells method when you are not sure whether they exist or not.

¹_{The concept of breaking something just to see if it exists has always seemed just plain wrong to me.}

Type Mismatch error

2 Answers