Solved

# Excel Formula Confusion

Posted on 2011-05-06
213 Views
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

0
Question by:cpatte7372

LVL 22

Expert Comment

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 Comment

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

LVL 22

Expert Comment

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

LVL 22

Expert Comment

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

LVL 85

Expert Comment

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 Comment

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

LVL 85

Expert Comment

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 Comment

rorya,

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

Author Comment

rorya,

It won't accept the following:

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

LVL 85

Expert Comment

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

Author Comment

I get a runtime error 13

Type mismatch
0

LVL 85

Accepted Solution

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 Comment

rorya,

That worked.

You're a star!

Cheers mate...
0

Author Closing Comment

Rorya is D Man!
0

## Join & Write a Comment Already a member? Login.

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

#### 754 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!