Type mismatch on values: 'runtime error 13'

0
votes

I was running my macro perfectly then all of a sudden the macro started to show runtime error 13' Type mismatch and I can't figure it out. The part that's always getting highlighted is:

  If Cells(x, "E") >= Num1 And Cells(x, "E") <= Num2 Then

and I think I coded it correctly because my other macros are coded that way but for some reason this is not working.

Sub PPM()

Dim RawData As Worksheet
Dim MatchData As Worksheet


Set MatchData = Worksheets("MATCH")
Set Pastesheet = Worksheets("PASTE")

Pastesheet.Select
Pastesheet.Range("$A$3:$F$5000").Clear

MatchData.Select

Application.ScreenUpdating = False



Set Num1 = MatchData.Range("$I$3")
Set Num2 = MatchData.Range("$K$3")

Dim x As Long

For x = 6 To 5000

If Cells(x, "E") >= Num1 And Cells(x, "E") <= Num2 Then

 Cells(x, "A").Resize(, 6).Copy

Pastesheet.Cells(Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial _
xlPasteValuesAndNumberFormats


    End If

Next x

Application.ScreenUpdating = True

Pastesheet.Select

MsgBox "Search Complete"

End Sub
2
vbaexcel
What is the value of x when the error first happens? – Excel Hero
Similarly - what's the value of Cells(x, "E") when you see the error? – Tim Williams
A Shot in the dark: Your cell Cells(x, "E") has a formula error like #N/A or similar – Siddharth Rout
I actually figured it out, some of the cells in range E had a #N/A in it and had to add a formula to format that, THANKS GUYS! – Sergio Oropeza

2 Answers

0
votes

I would check if MatchData.Range("$I$3"),MatchData.Range("$K$3")and the cells from MatchData.Range("E6") to MatchData.Range("E5000") are numeric. I hope it will help you.

0
votes

You should always declare the types of your variables. It makes it so much easier to debug. Currently you're comparing ranges using a numeric comparison:

Set Num1 = MatchData.Range("$I$3")
Set Num2 = MatchData.Range("$K$3")
...
If Cells(x, "E") >= Num1 And Cells(x, "E") <= Num2 Then

VBA helps you in that it will compare numbers if the cells contain numbers - however, it will fail gruesomely if the cells contain text because the <= and => operators are undefined for strings.

I would suggest something like this. First a helper function:

Private Function GetInt(rng as Range) As Integer
   On Error Resume Next
   GetInt = CInt(rng.Value)
End Function

Then change your code to:

Dim Num1 As Integer, Num2 As Integer
Num1 = GetInt(MatchData.Range("$I$3"))
Num2 = GetInt(MatchData.Range("$K$3"))
...
If GetInt(Cells(x, "E")) >= Num1 And GetInt(Cells(x, "E")) <= Num2 Then

Now you're code will always be safe from unexpected data in your cells. Note that I assume two things:

  • It is alright for the code to assume a value of 0, if the cells contain text
  • You're cells contain integers. If they contain decimal numbers, you should change the helper function to this:
Private Function GetDouble(rng as Range) As Double
   On Error Resume Next
   GetDouble = CDbl(rng.Value)
End Function