[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

Excel Run-time error 13

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
cpatte7372
Asked:
cpatte7372
  • 4
  • 4
1 Solution
 
Rory ArchibaldCommented:
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
 
cpatte7372Author Commented:
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
 
Rory ArchibaldCommented:
Just noticed that you never closed the other question, so why have you started a new one?
0
Independent Software Vendors: 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!

 
cpatte7372Author Commented:
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
 
Rory ArchibaldCommented:
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
 
cpatte7372Author Commented:
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
 
Rory ArchibaldCommented:
I need more to go on than "all sorts of errors". :)
0
 
cpatte7372Author Commented:
Hi rorya,

Will let you know the errors in a bit - currently trading....
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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