Solved

Excel Run-time error 13

Posted on 2011-03-15
10
283 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Expert Comment

by:Ingeborg Hawighorst
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
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…

696 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