VBA comparing cell values dosen't work consistantly

[michaelehoudek]

I have the game scoring worksheet pictured below. Everything works fine up to 6 players. If I add another, the line "If Range("H" & rownum) <> Range("J11").Value Then" doesn't work as it should and triggers Exit Sub. I added the MsgBox line above to verify H and row matchs J11, which it always does. Can't firgure out why it works till J11 is 0.0007 or higher! Any ideas? Thanks, Mike

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rownum As Integer, CellValue As String, Game As String, MyInput As Integer, Selected
On Error GoTo ErrorHandler
'Sheets("Results").Protect UserInterfaceOnly:=True
Game = Sheets("Input").Range("Game")
rownum = ActiveCell.Row
Selected = ("$L" & "$" & (25 - MyInput))
numselect = Range("$A$1").Value
If Game = "Chickenfoot" Then
If (ActiveCell.Row) = numselect Then
Exit Sub
Else
        MsgBox (Range("H" & rownum).Value & "   " & Range("J11").Value)        'THIS LINE NORMALLY ISN'T HERE, JUST DONE TO CONFIRM VALUES ARE THE SAME
If Range("H" & rownum) <> Range("J11").Value Then
Exit Sub
End If
MyInput = InputBox("Domino number selection", "Enter Number Entering Score For, or Press Enter if on right row")
    ActiveSheet.Range("$A$1") = 25 - MyInput
If (ActiveCell.Address) = Selected Then
Application.EnableEvents = True
Exit Sub

 

[StephenCrump]

This looks like a floating point arithmetic rounding difference.

In the workbook, try the formula: =H13=J$11, and copy down. You should find there's a FALSE value somewhere, i.e. a tiny rounding difference?

 

[michaelehoudek]

This looks like a floating point arithmetic rounding difference.

In the workbook, try the formula: =H13=J$11, and copy down. You should find there's a FALSE value somewhere, i.e. a tiny rounding difference?

Good idea. Came up as TRUE all the way down. This really has me perplexed! Thanks, Mike

 

[StephenCrump]

In that case, are you sure it's this piece of code that's the problem? You can put a breakpoint on the Exit Sub line to test.

If Range("H" & rownum) <> Range("J11").Value Then
    Exit Sub
End If

You've only posted part of your code, and it's not at all clear what it's doing, or why you're using the SelectionChange event. You have Exit Sub in two other places. But what makes you sure that Exit Sub is being triggered at all?

The code looks a bit muddled in any event, e.g. this line occurs before MyInput has a value

Selected = ("$L" & "$" & (25 - MyInput))

Later on, you test:

If (ActiveCell.Address) = Selected Then

and my guess is that you meant to update Selected based on the value of the InputBox? But your code doesn't do this, so Selected still has its initial value.

I suggest you put a breakpoint at the top of your Sub and step through line by line to test what is happening.

 

[michaelehoudek]

You're right about my muddled code. I'm no pro at this, I just keep trying things till I get it to work. Your comment about MyInput not having a value taught me a bit about how to walk through my code to see what doesn't make sense. Thank You for that!

Putting in the break point is how I found the line that broke down for me: If Range("H" & rownum) <> Range("J11").Value Then
The thing that I can't figure is why it doesn't work after 6 players, since H coulmn is calculated by multipling .0001 x $ op players and J11 does the exact same math. H coulmn only changes after I enter the first score.

Copying =H13=J$11 gives me true no matter how many players, yet when I added a VBA line MsgBox Range("H" & rownum).Value = Range("J11").Value it gives me false after 6 players, should be doing the exact same math I thought. I tried to subtract H13 from J11 and J11 from H13 in the VBA to determine what the difference was and the answer I got was -1.0842021724855E-19... Didn't help me determine what was going on.

However, your idea "try the formula: =H13=J$11, and copy down. You should find there's a FALSE value somewhere, i.e. a tiny rounding difference?" gave me an idea - I solved the problem by changing the line - If Range("H" & rownum) <> Range("J11").Value Then to - If Round(Range("H" & rownum).Value) <> Round(Range("J11").Value) Then NOW IT WORKS. I don't know why and it bugs me that I don't know why, but at least by trying to figure it out your line of thinking made it work and was valuable to me.

Thanks again, Mike

 

[StephenCrump]

Great, I'm glad you worked it out.

The thing that I can't figure is why it doesn't work after 6 players, since H coulmn is calculated by multipling .0001 x $ op players and J11 does the exact same math ...

... I tried to subtract H13 from J11 and J11 from H13 in the VBA to determine what the difference was and the answer I got was -1.0842021724855E-19...

Here's the explanation: Floating-point arithmetic may give inaccurate result in Excel - Microsoft 365 Apps

And an example to illustrate:

	A	B	C	D	E
1	1	0.3333	0.3333	TRUE
2	2	0.6667	0.6667	TRUE
3	3	1.0000	1.0000	TRUE
4	4	1.3333	1.3333	TRUE
5	5	1.6667	1.6667	TRUE
6	6	2.0000	2.0000	TRUE
7	7	2.3333	2.3333	TRUE
8	8	2.6667	2.6667	TRUE
9	9	3.0000	3.0000	TRUE
10	10	3.3333	3.3333	TRUE
11	11	3.6667	3.6667	TRUE
12	12	4.0000	4.0000	TRUE
13	13	4.3333	4.3333	TRUE
14	14	4.6667	4.6667	TRUE
15	15	5.0000	5.0000	TRUE
16	16	5.3333	5.3333	TRUE
17	17	5.6667	5.6667	TRUE
18	18	6.0000	6.0000	TRUE
19	19	6.3333	6.3333	TRUE
20	20	6.6667	6.6667	FALSE	-2.7E-15
21	21	7.0000	7.0000	TRUE
22	22	7.3333	7.3333	TRUE
23	23	7.6667	7.6667	FALSE	-3.6E-15
24	24	8.0000	8.0000	TRUE
25	25	8.3333	8.3333	TRUE
26	26	8.6667	8.6667	FALSE	-1.8E-15
Sheet1

Cell Formulas
Range	Formula
B1	B1	=1/3
C1:C26	C1	=A1/3
D1:D26	D1	=C1=B1
E1:E26	E1	=IF(D1,"",B1-C1)
B2:B26	B2	=B1+1/3

 

[michaelehoudek]

Thank you! It was really bugging me that while I'm not a math expert, I knew the two numbers should be exactly the same. I appreciate your tenacity!