Solved

Excel Runtime Error 13: Type Mismatch

Posted on 2011-03-07
5
300 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 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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Suggested Solutions

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
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 …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

734 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