Excel VBA runtime error '13' type mismatch

Question

Sub compareLines()

    Application.ScreenUpdating = False
    ActiveSheet.Cells(3, 3).Activate

    While ActiveCell.Value <> ""

        If ActiveCell.Value - ActiveCell.Offset(-1, 0).Value < 0 Then

            ActiveCell.EntireRow.Delete

        Else

            ActiveCell.Offset(1, 0).Activate

        End If

    Wend

    Application.ScreenUpdating = True

    End Sub

This is my code that I'm currently the error on.

The error is Excel VBA runtime error "13 type mismatch.

The line the error is on: If ActiveCell.Value - ActiveCell.Offset(-1, 0).Value < 0 Then

This code had worked on previous worksheets, but when I import this macro onto a new worksheet, it doesn't seem to work. All the solution I have found on SO don't seem to apply to my circumstance, so any help on this problem would be greatly appreciated.

A sample of the data I'm using:

Which line throws the error? Also, I would recommend that you have this in a Module and go throw the sheets as you need from there — Zac, Jul 28 '17 at 12:39
@Zac `If ActiveCell.Value - ActiveCell.Offset(-1, 0).Value < 0 Then` — Aydan Howell, Jul 28 '17 at 12:41
what are the values of `ActiveCell.Value` and `ActiveCell.Offset(-1, 0).Value` ? — braX, Jul 28 '17 at 12:41
@braX They are always in increasing, but only numbers at the moment. Something like 3, 3, 3, 3, 3, 3, 2, 4, 4, 4, — Aydan Howell, Jul 28 '17 at 12:43
that did not answer my question. what are their specific values? use debug — braX, Jul 28 '17 at 12:45
You cannot start this code from row 1. Start from row 2 and make sure all is numeric in the column. — A.S.H, Jul 28 '17 at 12:46
`Type Mismatch` would occur if one of your values isn't being treated as a number. I'd suggest commenting out your `ScreenUpdating` lines and stepping through your code using `F8`. When it throws the error check the activecell and the cell above. — Darren Bartrup-Cook, Jul 28 '17 at 12:46
@DarrenBartrup-Cook @braX Turns out one of the values has letters in: `0035Feldwicke, William,7, Kew Street ` — Aydan Howell, Jul 28 '17 at 12:51
so you need to identify what the program * should* do, when it encounters a non-numeric value. Should it skip? Or should it delete? Should it do something else? You can use an `If` block to anticipate this, or you could go with a structured error handler, etc. — David Zemens, Jul 28 '17 at 12:53
https://snag.gy/ZhM2X4.jpg This is what the data looks like. The thing is, the script would ignore this, now it's not. @DavidZemens — Aydan Howell, Jul 28 '17 at 12:53
I guess you could check if the ActiveCell and the Offset Cell `IsNumeric` and only perform the calculation if they both are. — Darren Bartrup-Cook, Jul 28 '17 at 12:59
Or simply write `On Error Resume Next` after `Sub compareLines()` and see how it works. — Vityata, Jul 28 '17 at 13:00
@DarrenBartrup-Cook How would one do that? I'm rather new to this. — Aydan Howell, Jul 28 '17 at 13:00
Wrap your existing `If...End If` in `If IsNumeric(ActiveCell) And IsNumeric(ActiveCell.Offset(-1, 0)) Then..... End If` — Darren Bartrup-Cook, Jul 28 '17 at 13:04
Another possibility is that by the looks of it your data always has four instances of `/` and the last always starts with four digits which is what you're after. This formula will strip away the text if your original data is in column C6: `=LEFT(MID(C6,FIND("~",SUBSTITUTE(C6,"/","~",4))+1,LEN(C6)),4)` — Darren Bartrup-Cook, Jul 28 '17 at 13:06
Another possibility - highlight your data and hit the `Remove Duplicates` button. — Darren Bartrup-Cook, Jul 28 '17 at 13:07
I recommend you not to use `ActiveCell` in your code. See https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros — Rafael Matos, Jul 28 '17 at 13:11
@Vityata Managed to make it happen, after all that, a simple solution was at hand. Sorry for the confusion and thanks for all the help! <3 Post the answer and I will tick it for you man. — Aydan Howell, Jul 28 '17 at 13:13
@AydanHowell - if I post `On Error Resume Next` as an answer it would be downvoted, trust me. — Vityata, Jul 28 '17 at 13:14
Because `On Error Resume Next` is a hack :) It will "work" in very limited, specific circumstances, perhaps such as this one, but it is a bad programming habit to rely on that construct. — David Zemens, Jul 28 '17 at 13:19

score 1 · Accepted Answer · edited May 08 '18 at 21:49

In general, On Error Resume Next is something that you should be extremely careful with.

If you put it in your code, it would start ignoring errors and if someone works with code after you he would not be happy at all (or he would think you are an amateur or job-defender). Having said that, CPearson has a good article about it, that's worthy to read - http://www.cpearson.com/excel/errorhandling.htm

Last but not least, make sure that you change On Error Resume Next to something else, once you realize why the errors are happening.

In your case, its a good idea to use the IsNumeric function, to avoid the TypeMismatch Error. If other errors appear, try to avoid them with a similar matter.

Dim v1 as Range, v2 as Range
While ActiveCell.Value <> ""
    Set v1 = ActiveCell.Value
    Set v2 = ActiveCell.Offset(-1)
    If IsNumeric(v1) And IsNumeric(v2) Then
        'Both are numeric, so it's safe to perform arithmetic against these values
        If v1 - v2 < 0 Then
            ActiveCell.EntireRow.Delete
        Else
            ActiveCell.Offset(1, 0).Activate
        End If
    Else: ActiveCell.Offset(1, 0).Activate
    End If
Wend

Excel VBA runtime error '13' type mismatch

1 Answers1