Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 335
  • Last Modified:

Excel Runtime Error 13: Type Mismatch

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
cpatte7372
Asked:
cpatte7372
1 Solution
 
Ardhendu SarangiSr. Project ManagerCommented:
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
 
sdwalkerCommented:
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
 
cpatte7372Author Commented:
Thanks chaps for responding. Going to take another look at it. Back in 20minutes.

Cheers
0
 
Chris BottomleyCommented:
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
 
cpatte7372Author Commented:
Excellent
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now