Solved

Excel Runtime Error 13: Type Mismatch

Posted on 2011-03-07
5
294 Views
Last Modified: 2012-05-11
Hello Experts,

Whenever I open the attached spreadsheet or make any changes I keep on getting the Type Mismatch Error. When I debug it takes me to:

If xValue <> "" And xValue2 <> "" And Round(xValue, 2) = Round(xValue2, 2) Then


However, I have no idea what to do. The code was written by another expert.

I wonder if someone could take a look at it and let me know what is needed to resolve it?

Cheers

Carlton
EE-1-.xlsm
0
Comment
Question by:cpatte7372
5 Comments
 
LVL 20

Expert Comment

by:pari123
ID: 35056596
Hi,
you are getting the type mismatch error because of the values on the Symbol column.

The following line here - If xValue <> "" And xValue2 <> "" And Round(xValue, 2) = Round(xValue2, 2) Then expects this to be number or numeric and gets confused when it finds a character.

Now i am not sure what your requirements were for creating this macro, so can you please explain what do you this macro to do?

if you want to get this working as-is, then remove the text values from the symbol column and it should work.

let me know,

thx,
Ardhendu
0
 
LVL 12

Expert Comment

by:sdwalker
ID: 35056607
It looks like it's comparing the wrong rows.  The reason it's breaking is it's trying to round the letter "A" to 2 decimal places, which is difficult, considering it's not a number.  I'm not sure what you SHOULD be comparing, but it's comparing column L to column D.  Maybe it should be comparing column O to column D?

Anyway, that's what the problem is.

Let me know if you have other questions.

sdwalker
0
 

Author Comment

by:cpatte7372
ID: 35056697
Thanks chaps for responding. Going to take another look at it. Back in 20minutes.

Cheers
0
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 500 total points
ID: 35056751
I have modified Worksheet_Calculate as below.  Basically although you chjeck for xValue and xValue2 as blank you still execute the command to round the blanks.  WHat the change does is to split the blank check from the round out.

Chris
Private Sub Worksheet_Calculate()
Dim xCell As Range
Dim xLastRow As Long
Dim xValue As Variant
Dim xValue2 As Variant
Dim Point02 As Boolean


xLastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

If xLastRow < 2 Then Exit Sub


For Each xCell In Range("r2:r" & xLastRow)
Debug.Print xCell.Row
    xValue = xCell.Value
    xValue2 = xCell.Offset(0, -14).Value
    If Not IsError(xValue) And Not IsError(xValue2) Then
        If xValue <> "" And xValue2 <> "" Then
            If Round(xValue, 2) = Round(xValue2, 2) Then
              PlayTheSound "windows xp information bar.wav"
                Cells(1, 18).Interior.Color = 255
                Exit Sub
            End If
        End If
    End If
Next

Cells(1, 18).Interior.Color = 65535
If Not Point02 Then Cells(1, 12).Interior.Color = 65535

For Each xCell In Range("l2:l" & xLastRow)
    xValue = xCell.Value
    xValue2 = xCell.Offset(0, -8).Value
    If Not IsError(xValue) And Not IsError(xValue2) Then
        If Not IsError(xValue) And Not IsError(xValue2) Then
            If xValue <> "" And xValue2 <> "" Then
                PlayTheSound "Windows Information Bar.wav"
                Cells(1, 12).Interior.Color = 255
                Exit Sub
            End If
        End If
      End If
    Next

Cells(1, 12).Interior.Color = 65535



Call CheckD
End Sub

Open in new window

0
 

Author Closing Comment

by:cpatte7372
ID: 35306101
Excellent
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question