Solved

Excel Run-time error 13

Posted on 2011-03-15
10
281 Views
Last Modified: 2012-05-11
Hello Experts,

I wonder if you can help me.

Everytime I open the attached spreadsheet I get the Run-time error message 'Type Mismatch'. When I click debug it highlights:
 
If xValue <> "" And xValue2 <> "" And Round(xValue, 2) = Round(xValue2, 2) Then

So when I deleted the whole function and I get the same error message but this time it points to the other debug highlight below

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

This was written for me by another expert to alert when a certain condition occurs.

I should point out the alerts don't work either (but that's another issue)

I'm not particularly bothered by the function where it says:
For Each xCell In Range("v2:v" & xLastRow)

But I would like the function to work and give me an alert under where it says:

For Each xCell In Range("p2:p" & xLastRow)

My guess is the problem lies with the xcell.offset:
 xValue2 = xCell.Offset(0, -10).Value
But I'm not expert.

As always, thanks in advance for you help

Carlton
ExpertsTrading.xlsm
0
Comment
Question by:cpatte7372
  • 4
  • 4
10 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
I'm sure this has been asked before? You can't test for "" and try Round in the same line (since ALL conditions will be evaluated) so you need:
If xValue <> "" And xValue2 <> "" Then
   If Round(xValue, 2) = Round(xValue2, 2) Then

' your code here
   End If
End If

Open in new window

0
 

Author Comment

by:cpatte7372
Comment Utility
rorya

You're right. I had asked this before. However, I thought I found a solution, but it failed.

I will try you're suggestion

Cheers

Carlton
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
Just noticed that you never closed the other question, so why have you started a new one?
0
 

Author Comment

by:cpatte7372
Comment Utility
rorya

I pasted your code in (see below) and then I got the following error message 'Compile error: Next without For.

So I removed the next and then I got the following Compille error: For control variable already in use.

For Each xCell In Range("v2:v" & xLastRow)
    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

            Cells(1, 18).Interior.Color = 255
            Exit Sub
        End If
    End If
Next


0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
Comment Utility
You'll notice my code had 2 End If statements as there were 2 If statements. Yours is one short:
For Each xCell In Range("v2:v" & xLastRow)
    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

            Cells(1, 18).Interior.Color = 255
            Exit Sub
         End If
        End If
    End If
Next xCell

Open in new window


You still didn't say why you opened a new question?

0
 

Author Comment

by:cpatte7372
Comment Utility
Rorya

Fantastic mate.

Can you I ask you one last favour? (Actually you may written the following code)

You will see in the spreadsheet the following code:

Call CheckD
End Sub
Sub CheckD()
Dim ctr As Long
Dim Point02 As Boolean
If Not Point02 Then Cells(1, 5).Interior.Color = 65535
For ctr = 2 To 65000
    If Range("E" & ctr) = "" Then Exit Sub
    If Range("E" & ctr) = Round(Range("P" & ctr).Value, 2) - 0.02 Then
          PlayTheSound "Windows XP Print complete.wav"
          Cells(1, 5).Interior.Color = vbCyan
          End If
        Next
End Sub


It basically alerts me when cells in column E are lest than the value of cells in column P by -0.02.

I have been trying to simply copy the code to also alert me when values in col E are equal to values in Col P.

I simply tried the following:

Call CheckC
End Sub
Sub CheckC()
Dim ctr As Long
Dim Point02 As Boolean
If Not Point02 Then Cells(1, 5).Interior.Color = 65535
For ctr = 2 To 65000
    If Range("E" & ctr) = "" Then Exit Sub
    If Range("E" & ctr) = Round(Range("P" & ctr).Value, 2) Then
          PlayTheSound "tada.wav"
          Cells(1, 5).Interior.Color = vbCyan
          End If
        Next
End Sub

I just simply replaced Call CheckD to Call CheckC
By itself it works fine but when I try to add it I keep on getting all sort of errors.

As always, really appreciate your help
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
I need more to go on than "all sorts of errors". :)
0
 

Author Comment

by:cpatte7372
Comment Utility
Hi rorya,

Will let you know the errors in a bit - currently trading....
0
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

728 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

13 Experts available now in Live!

Get 1:1 Help Now