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

# 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

Thanks again experts

Carlton

0
cpatte7372
• 7
• 4
• 3
1 Solution

Commented:
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

Author 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

Commented:
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

Commented:
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

Commented:
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

Author 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

Commented:
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
``````
0

Author Commented:
rorya,

Thanks for getting back going to check it now....
0

Author Commented:
rorya,

It won't accept the following:

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

Commented:
Define "won't accept" please. (I didn't alter that line in any way)
0

Author Commented:
I get a runtime error 13

Type mismatch
0

Commented:
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
``````
0

Author Commented:
rorya,

That worked.

You're a star!

Cheers mate...
0

Author Commented:
Rorya is D Man!
0

## Featured Post

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