Solved

Excel Run-time error 13

Posted on 2011-03-15
10
287 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
[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
  • 4
  • 4
10 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35138251
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
ID: 35138352
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
ID: 35138372
Just noticed that you never closed the other question, so why have you started a new one?
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

Author Comment

by:cpatte7372
ID: 35138482
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
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 35138566
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
ID: 35138925
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
ID: 35139034
I need more to go on than "all sorts of errors". :)
0
 

Author Comment

by:cpatte7372
ID: 35139453
Hi rorya,

Will let you know the errors in a bit - currently trading....
0
 
LVL 50
ID: 35744606
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
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.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

691 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