?
Solved

Excel Runtime Error 13: Type Mismatch

Posted on 2011-03-07
5
Medium Priority
?
305 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 20

Expert Comment

by:Ardhendu Sarangi
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 2000 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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

765 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