0

I would like to compare two cells and it should display "OK" or "NOT OK".

When I used an excel formula, it worked properly.

However when I tried it using VBA, I am not getting the correct results.

Below is a simpler example of my data:

Fee1 Fee2
0.009 0.009

To note that in my worksheet, Fee1 is generated via a VBA vlookup function and Fee2 is generated via a case statement VBA code.

My code is as per below:

If Range("L2") <> Range("M2") Then
    Range("N2").Value = "NOT OK"
Else
    Range("N2").Value = "OK"
End If

Debug.Print Range("N2")
Debug.Print Range("L2")
Debug.Print Range("m2")

I used debug print to check and got the results below:

NOT OK
0.009 
0.009

I am perplexed at what i am doing wrong.

GoWiser
  • 857
  • 6
  • 20
Nini
  • 13
  • 3
  • and `Fee1` is which column? `M`? `N`? `L`? – braX Aug 27 '21 at 06:52
  • 1
    Try to print the difference between Range("L2").Value and Range("M2").Value, and find the conclusion that those values are not equal. – Luuk Aug 27 '21 at 06:54
  • the statement If Range("L2") <> Range("M2") Then compere ranges , and L2 is not M2 range, you should compere values like If Range("L2").value <> Range("M2").value Then – xShen Aug 27 '21 at 06:56
  • 1
    "IF-THEN-ELSE-END IF" always works in VBA. You only must understand what the code compares against **what you think it compares**... If the two values are differently calculated, the maybe are different on a 19th decimal. Try rounding the values at the same number of decimals. – FaneDuru Aug 27 '21 at 07:03
  • @braX Fee1 is L and Fee2 is M then I wanted to display "NOT OK" or "OK" in N – Nini Aug 27 '21 at 07:05
  • @xShen The statement `If Range("L2") <> Range("M2")` [compares values](https://stackoverflow.com/a/18051644/11683), not ranges. – GSerg Aug 27 '21 at 07:06
  • @Luuk Yes I am getting a difference of 1.73472347597681E-18 when I debug print the difference. What i found weird is that when I used the excel formula, it is working properly – Nini Aug 27 '21 at 07:07
  • @Nini Because you are using [precision as displayed](https://learn.microsoft.com/en-US/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-result#method-2-precision-as-displayed)? – GSerg Aug 27 '21 at 07:29
  • @GSerg Thanks a lot! I added it to my code and it works!! – Nini Aug 27 '21 at 07:38
  • @Nini You should have consulted https://stackoverflow.com/q/4915462/11683 instead. – GSerg Aug 27 '21 at 07:39

1 Answers1

0

You might want to compare the values with a limited number of decimals like:

If Round(Range("L2").Value, 3) <> Round(Range("M2").Value, 3) Then
GoWiser
  • 857
  • 6
  • 20