Run time error 13 Type Mismatch in program module which ran successfully earlier-CodePudding

Sample Data :

0votes
https://stackoverflow.com/questions/32382401/autohide-multiple-rows-in-excel/32383360#32383360
vbaexcel
answered Sep 3, 2015 at 18:53
0votes
Accepted
https://stackoverflow.com/questions/32273121/corretc-excel-vba-macro-to-return-the-values-as-text-and-as-date/32273219#32273219  'clickable format
vbaexcel
answered Aug 28, 2015 at 14:18

I want to insert a row between votes and url line if the answer is not accepted and url immediately follws votes line with a purpose to make grouping of 5 rows for ultimately transposing data in a single row. Code use by me is as follows :

Sub insertrow()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet2")
Last = Cells(Rows.Count, "A").End(xlUp).Row
For i = Last To 1 Step -1
    If (Cells(i, "A").Value) Like "*vote*" And (Cells(i   1, "A").Value) <> "Accepted" Then
        Cells(i   1, "A").EntireRow.Insert
    End If
Next i
End Sub

I am getting Run time error 13 Type mismatch on the following line although this program ran succesfully yesterday night for simmiliar data.

       ` If (Cells(i, "A").Value) Like "*vote*" And (Cells(i   1, "A").Value) <> "Accepted" `

Any help shall be appreciated very much.

CodePudding user response：

Did a quick test with the data and the code you provided and had no issues. However, there is a problem with the code: Although you assign a specific sheet to a variable, you continue to work with the ActiveSheet. It is likely that this sheet is not the sheet with your sample data and may contain data that lead to the type mismatch error.

When programming VBA in Excel, you should always qualify exactly on which worksheet you want to work. One way is to use a With-statement. Take care that before every instance of Cells, Range, Rows and Columns you add a dot - that tells VBA you are referring to the object of the With-statement.

Sub insertrow()
    With ThisWorkbook.Sheets("Sheet2")
        Dim Last As Long, i As Long
        Last = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = Last To 1 Step -1
            If (.Cells(i, "A").Value) Like "*vote*" _
            And (.Cells(i   1, "A").Value) <> "Accepted" Then
                .Cells(i   1, "A").EntireRow.Insert
            End If
        Next i
    End With
End Sub

Or, if you like that better:

Sub insertrow()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet2") 
    
    Dim Last As Long, i As Long
    Last = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    For i = Last To 1 Step -1
        If (ws.Cells(i, "A").Value) Like "*vote*" _
        And (ws.Cells(i   1, "A").Value) <> "Accepted" Then
            ws.Cells(i   1, "A").EntireRow.Insert
        End If
    Next i
End Sub

Errors like type mismatch usually can be revealed rather easy by checking the related data (in this case content of 2 cells) with the debugger.