Solved

Excel Runtime Error 13: Type Mismatch

Posted on 2011-03-07
5
292 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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now