• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 253
  • Last Modified:

Excel Formula Confusion

Hello Experts,

Can someone please tell me why the following code will provide an alert in the 'Trarding 7th April' Worksheet;
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


However, the following virtually identical code won't work in the 'Trading''

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("O" & ctr).Value, 2) - 0.02 Then
          PlayTheSound "Windows XP Print complete.wav"
          Cells(1, 5).Interior.Color = vbCyan
          End If
        Next
End Sub

I have attached the spreadsheet.

Thanks again experts

Carlton

EETrading.xlsm
0
cpatte7372
Asked:
cpatte7372
  • 7
  • 4
  • 3
1 Solution
 
rspahitzCommented:
Without looking too deeply, I see this:

 If Range("E" & ctr) = Round(Range("P" & ctr).Value, 2) - 0.02 Then

Whenever you work with decimal values, there's the risk of rounding problems internal to the processor or Excel.
The preferred way is to either convert to integer first:

 If Range("E" & ctr)*100 = Round(Range("P" & ctr).Value, 2)*100 - 2 Then

or to use a comparison that does not involve equality:

 If Range("E" & ctr) > Round(Range("P" & ctr).Value, 2) - 0.03 AND _
Range("E" & ctr) < Round(Range("P" & ctr).Value, 2) - 0.01 Then
0
 
cpatte7372Author Commented:
rspahitz

I don't think I explained myself very well.

The problem is I can't get the code to work in the 'Trading' worksheet - even when I change it too

If Range("E" & ctr)*100 = Round(Range("O" & ctr).Value, 2)*100 - 2 Then

Note, that I've also changed ("P" & ctr) to ("P" & ctr). This is where I think the problem lies....

Cheers
0
 
rspahitzCommented:
Are you able to use the debugger to find out where it's going astray?  (F8 is great!)  There are also other tracking techniques that can help here, like breakpoints and watches...
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
rspahitzCommented:
Based on the attached spreadsheet, I'm seeing a lot of missing data so it's difficult to determine your real goal here.  Are you trying to color the background if one field matches another?
0
 
Rory ArchibaldCommented:
You start checking in row 2 of column E which is blank on Trading (it isn't on the other sheet) so the sub exits.
0
 
cpatte7372Author Commented:
rspahitz/rorya,

Thanks for getting back to me. Sorry its taken so long to respond.

rspahitz, I'm trying to get an alert and a coloured background in both worksheets. For example, in the example if you were to type 104.14 in E2 you will get an alert and coloured cell in the worksheet 'Trading 7th April'. However, if you type in 170.14 in worksheet 'Trading' you'll just get a highlighted cell but no alert. I would also like the alert.

If there are any experts out there that may have suggestion it would be very welcomed.

Cheers
0
 
Rory ArchibaldCommented:
As I said, your E2 cell on the Trading sheet is blank so the sub simply exits without doing anything. Try:
Sub CheckD()
Dim ctr As Long
Dim Point02 As Boolean
If Not Point02 Then Cells(1, 5).Interior.Color = 65535
For ctr = 3 To Cells(Rows.Count, "E").End(xlUp).Row
    If Range("E" & ctr).Value = Round(Range("O" & ctr).Value, 2) - 0.02 Then
          PlayTheSound "Windows XP Print complete.wav"
          Cells(1, 5).Interior.Color = vbCyan
          End If
        Next ctr
End Sub

Open in new window

0
 
cpatte7372Author Commented:
rorya,

Thanks for getting back going to check it now....
0
 
cpatte7372Author Commented:
rorya,

It won't accept the following:

If Range("E" & ctr).Value = Round(Range("0" & ctr).Value, 2) - 0.02 Then
0
 
Rory ArchibaldCommented:
Define "won't accept" please. (I didn't alter that line in any way)
0
 
cpatte7372Author Commented:
I get a runtime error 13

Type mismatch
0
 
Rory ArchibaldCommented:
That may be due to the other merged cells then:
Sub CheckD()
Dim ctr As Long
Dim Point02 As Boolean
If Not Point02 Then Cells(1, 5).Interior.Color = 65535
For ctr = 3 To Cells(Rows.Count, "E").End(xlUp).Row
   If IsNumeric(Range("E" & ctr).Value) Then
    If Range("E" & ctr).Value = Round(Range("O" & ctr).Value, 2) - 0.02 Then
          PlayTheSound "Windows XP Print complete.wav"
          Cells(1, 5).Interior.Color = vbCyan
    End If
   End If
Next ctr
End Sub

Open in new window

0
 
cpatte7372Author Commented:
rorya,

That worked.

You're a star!

Cheers mate...
0
 
cpatte7372Author Commented:
Rorya is D Man!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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