Error 13: Type mismatch having to do with subtraction

Question

I'm writing a macro and I'm having a hard time understanding excel. The macro check if certain cells are the same, and if the subtraction of cells is less than 103, then it executes code. I'm getting stuck when I try to subtract two cells and assign it to a variable. Any Pointers?

Sub range1()
    Dim numResult As Integer

    For CHARS = 1 To 20000

        If Cells(CHARS, 1) = "" Then CHARS = 20000

        For n = 1 To 50
            If ([CELLS(CHARS, 1)] = [CELLS(CHARS + 1, 1)]) Then
               If ([CELLS(CHARS, 4)] = [CELLS(CHARS + 1, 4)]) Then
                  numResult = Evaluate([CELLS(CHARS + 1, 2)] & "-" & [CELLS(CHARS, 3)]) '//ERROR
                  If (numResult < 103) Then
                     Cells(CHARS + 1, 3).Select
                     Selection.Cut Destination:=Cells(CHARS, 3)
                     Rows("(CHARS + 1):(CHARS + 1)").Select
                     Selection.Delete Shift:=xlUp
                  End If
               End If
            Else
                Exit For
            End If
         Next n

    Next CHARS
End Sub

For starters, you are missing and `End If`, which should be just below the `Next n` line. Also, your `numResult` line can be simplified to: `numResult = Cells(CHARS + 1, 2).Value - Cells(CHARS, 3).Value` — tigeravatar, Dec 10 '13 at 02:52
@tigeravatar: + 1. I had to discard my answer :) I would recommend putting it as an answer... — Siddharth Rout, Dec 10 '13 at 02:54
You might also want to see [THIS](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) — Siddharth Rout, Dec 10 '13 at 02:56
@tigeravatar Thank you, that got rid of my error 13. I had that End If but VB objects any time I put it in there. Anyone have an idea why? — user2817749, Dec 10 '13 at 03:03
BTW instead of `If Cells(CHARS, 1) = "" Then CHARS = 20000` you can use `If Cells(CHARS, 1) = "" Then Exit For` :) — Siddharth Rout, Dec 10 '13 at 03:06

Floris · Answer 1 · 2013-12-10T03:11:39.390

There are plenty of ways in which you could improve your code, but the bottom line is that if

 numResult = Evaluate([CELLS(CHARS + 1, 2)] & "-" & [CELLS(CHARS, 3)])

throws the error Type mismatch, then the things you are subtracting cannot be subtracted - or they cannot be assigned to an Integer. Most likely one of the cells contains text, or nothing.

I would recommend changing the line

Dim numResult As Integer

to

Dim numResult

That way, "any result" can be assigned to the variable, and you might be able to figure out what is going on.

EDIT if the End If would make things better, you have a problem understanding the structure of the If statement.

x = 3
If (1 = 1) Then x = 4
   MsgBox "hello"

results in x = 4, and the message "hello". But

x = 3
If (1 = 0) Then x = 4
   MsgBox "goodbye"

results in x = 3, and "goodbye". In other words - indentation alone doesn't cause the If to continue to the next line. For that you would need:

If (1 = 0) Then
  x = 4
  MsgBox "goodbye"
Else
  MsgBox "hello"
End If

So - you create a Block If when you have NOTHING AFTER THE Then - and then you need an End If. Otherwise, it's just the next statement after the Then that gets executed.

Does that help?

A thought: since you set CHARS to 20000 when you reach a blank cell, and you then execute the next code block, you will in fact try to evaluate (probably) - (blank minus blank) which cannot be put in an Integer… but I'm speculating a bit about the contents of your file, I must admit.

Good point about evaluating a blank cell. Yeah, I had a bit of trouble with End If, I'm a little more accustomed to brackets. — user2817749, Dec 10 '13 at 03:22

Error 13: Type mismatch having to do with subtraction

1 Answers1