Why Does Excel Say Cell Values Are Not Equal When They Are?

[nira123]

Not sure why it says cell values are not equal when they are equal. Following is my code and cell values are attached. I need to count cells values first and make sure first coordinate and last coordinate equal . Many thanks.


Sub coggv()

Dim i, j, tn As Integer
Dim a1, aa, ixv, iyv, cxx, cyy, cxv, cyv, cx1, cy1 As Double

n = 0
k = 0
aa = 0
ixv = 0
iyv = 0
cxx = 0
cyy = 0
u = 0


Do While Cells(6 + n, 12).Value <> ""

n = n + 1
Loop
tn = n
If (Cells(6 + tn - 1, 12) <> Cells(6, 12) Or Cells(6 + tn - 1, 13) <> Cells(6, 13)) Then

MsgBox ("Xn, Yn must be equal to X0, Yo")

Else
bla bla......

endif

 

[Jon Peltier]

First of all, this is wrong:

Dim i, j, tn As Integer
Dim a1, aa, ixv, iyv, cxx, cyy, cxv, cyv, cx1, cy1 As Double

This declares only tn as an integer and only cy1 as a double. The others are all variants.

Also, it's better to ignore Integers and use Longs (and the same rationale goes for Singles and Doubles).

You need to do it this way:

Dim i Long, j Long, tn As Long
Dim a1 As Double, aa As Double, ixv As Double, iyv As Double, cxx As Double, cyy As Double, cxv As Double, cyv As Double, cx1 As Double, cy1 As Double

Also, further down, use Cells.Value:

If (Cells(6 + tn - 1, 12).Value <> Cells(6, 12).Value Or Cells(6 + tn - 1, 13).Value <> Cells(6, 13).Value) Then

Second, you're probably running into the famous rounding error in the 15th significant digit. You should round these values or take other measures to eliminate tiny meaningless differences. Something like:

If Abs(Cells(6 + tn - 1, 12).Value / Cells(6, 12).Value) < 0.000001 Then

 

[nira123]

Hi Jon,
Many thanks for your response and correcting my mistakes. Wow, 15th significant digit did not know about that. I will try your suggestions and check whether it would help.

Kind Regards

 

[nira123]

Hi Jon, with your last point( 15th significant digit ) it worked brilliantly. I imported points from AutoCad, in excel it seemed equal even after clicking on the particular cells. I understand now it was due to rounding error in the 15th significant digit. Thank you so much.

Kind Regards,
Shan

 

[nira123]

First of all, this is wrong:

Dim i, j, tn As Integer
Dim a1, aa, ixv, iyv, cxx, cyy, cxv, cyv, cx1, cy1 As Double

This declares only tn as an integer and only cy1 as a double. The others are all variants.

Also, it's better to ignore Integers and use Longs (and the same rationale goes for Singles and Doubles).

You need to do it this way:

Dim i Long, j Long, tn As Long
Dim a1 As Double, aa As Double, ixv As Double, iyv As Double, cxx As Double, cyy As Double, cxv As Double, cyv As Double, cx1 As Double, cy1 As Double

Also, further down, use Cells.Value:

If (Cells(6 + tn - 1, 12).Value <> Cells(6, 12).Value Or Cells(6 + tn - 1, 13).Value <> Cells(6, 13).Value) Then

Second, you're probably running into the famous rounding error in the 15th significant digit. You should round these values or take other measures to eliminate tiny meaningless differences. Something like:

If Abs(Cells(6 + tn - 1, 12).Value / Cells(6, 12).Value) < 0.000001 Then

Following is the one I used to make that work. Hope this would be helpful for someone who got the same issue. It did not work well even I entered equal values manually in my worksheet until I tried Jon's last suggestion. Full credit to Jon.

v1 = Abs(Cells(6 + tn - 1, 12).Value / Cells(6, 12).Value)
v2 = Abs(Cells(6 + tn - 1, 13).Value / Cells(6, 13).Value)

If ((v1 < 0.99999 Or v1 > 1.00001) Or (v2 < 0.99999 Or v2 > 1.000001)) Then


MsgBox (" Your Polygon is not closed: Xn, Yn must be equal to X0, Yo  ")

Else
'bla bla....

end if