Beep/ding on an IF formula

  • Thread starter Thread starter Gerard Sanchez
  • Start date Start date
G

Gerard Sanchez

Hi

Formula:

=IF(OR((B72-B73)=B72,(B72-B73)=-B73,(B72-B73)=0),"",SUM(B72-B73))

I'd like to be able to make the computer beep/ding whenever B72<>B73 and
display difference between the two cells.

Any ideas?


Thanks!
 
Hi

Formula:

=IF(OR((B72-B73)=B72,(B72-B73)=-B73,(B72-B73)=0),"",SUM(B72-B73))

  I'd like to be able to make the computer beep/ding whenever B72<>B73 and
display difference between the two cells.

Any ideas?

Thanks!
Click to expand...
How do you want the code activated?

Sub Get_Difference()
Dim A As Range
Dim B As Range
Dim s As String
Dim t As String
Set A = Range("B72")
Set B = Range("B73")
s = "B72 is greater than B73 by "
t = "B73 is greater than B72 by "
If A > B Then MsgBox s & A - B
If B > A Then MsgBox t & B - A

End Sub
 
Try putting the following code in the Sheet module for the sheet
containing the cells to test. (Right click on the sheet tab and choose
View Code.)

Private Declare Function sndPlaySound32 Lib "winmm.dll" Alias _
"sndPlaySoundA" (ByVal lpszSoundName As String, _
ByVal uFlags As Long) As Long

Private Sub Worksheet_Calculate()
If Me.Range("B72").Value <> Me.Range("B73") Then
sndPlaySound32 "chimes", 1&
End If
End Sub


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 
Hi,

Disregarding the sound you want, lets look at the formula:

=IF(OR((B72-B73)=B72,(B72-B73)=-B73,(B72-B73)=0),"",SUM(B72-B73))

If B72-B73=B72 then B73=0
If B72-B73=-B73 then B72=0
If B72-B73=0 then B72=B73

So another way to write this formula would be:

=IF(OR(B72=0,B73=0,B72=B73),"",B72-B73)

FYI SUM(B72-B73) is bad style. Excel does what is inside the ()'s first and
then SUM's the results. When you subtract on number from another number you
have a new number. What is the SUM of a single number? - itself! So
constructions such as SUM(A1*A2), SUM(A1/A2), SUM(A1+A2) and yours are all
superfluous.
 
I guess I should have mentioned that but this condition would be applied to
3 columns of about 500 cells each!

i.e. A1<>A2 . . .A3<>A4 ....A5<>A6.... A7<>A8 . . .and so on . . . and on .
.. .

I was thinking along the line of creating a UDF to call on the function in a
conditional statement I would be able to along the entire column of about
1000 cells.
The problem is that IF formula only allows one event for either True or
False returns.

Are there any other function that would allow 2 events for either True or
False returns?

Can this be done with Choose(). Or are there any other ways to do this . .
..

Please help . . .

Thanks
 
I guess I should have mentioned that but this condition would be applied to
3 columns of about 500 cells each!

i.e. A1<>A2 . . .A3<>A4 ....A5<>A6.... A7<>A8 . . .and so on . . . and on .

I was thinking along the line of creating a UDF to call on the function in a
conditional statement I would be able to along the entire column of about
1000 cells.
The problem is that IF formula only allows one event for either True or
False returns.

Are there any other function that would allow 2 events for either True or
False returns?

Can this be done with Choose(). Or are there any other ways to do this . .
..
Please help . . .

Thanks
 
Back
Top