?
Solved

Excel Run-time error 13

Posted on 2011-03-15
10
Medium Priority
?
293 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 2000 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

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!
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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 will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

741 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