My If statement results in False when actually true

  • Thread starter Thread starter papa jonah
  • Start date Start date
P

papa jonah

Below is a portion of some code I have.
It works fine up to the point that I have noted. When I step through
this,
it works fine. Just before the step that I flagged below, I can use
the
intermediate window to determine the value of u, cells(u+1,"aa"), and
item:
?item
1
?cells(u+1, "aa").value
1
?u
1
The value in cell AA2 is 1.

Columns("aa:ad").NumberFormat = 0
On Error Resume Next
For Each cell In causecats
cats.Add cell.Value, Key:=cell.Text
Next
On Error GoTo 0
r = 0
For Each item In cats

Debug.Print item
r = r + 1
Cells(r + 1, "aa") = item

Cells(r + 1, "ab").Formula = "=sumproduct((" & causecats.Address &
"=""" & _
Cells(r + 1, "aa") & """)*(" & causebase.Address & "))"
Next

On Error Resume Next
For Each cell In causecats
revcats.Add cell.Value, Key:=cell.Text
Next
On Error GoTo 0
r = 0
j = 0
For Each item In revcats

Debug.Print item
r = r + 1
gFound = False
u = 0
For u = 1 To (causecats.Count + revcats.Count) * 2

'the result of the following line is false. I do not know why.

If item = Cells(u + 1, "aa") Then
Cells(u + 1, "ad").Formula = "=sumproduct((" & causecats.Address &
"="""
& _
Cells(u + 1, "aa") & """)*(" & causerev.Address & "))"
gFound = True
Exit For
End If
Next


Do I have some syntax problem? formatting problem? If item=1, cell AA2
=1,
and u=1, I do not understand why result of If item = Cells(u + 1,
"aa") is
false.

TIA for any ideas.



Expand AllCollapse All
 
papa jonah said:
Below is a portion of some code I have. ....
?item
1
?cells(u+1, "aa").value
1
?u
1
The value in cell AA2 is 1.
Click to expand...

Well, it may look like 1, but it might not be 1. Better to check

? Format(item - Cells(u + 1, "AA").Value, "0.000E+000")

This will show up *any* differences. If these differ by any amount, they
won't be equal. Also, unless they're the same data type, VBA can sometimes
become confused. So better to use

If Abs(CDbl(item) - Cells(u + 1, "AA").Value2) < 1E-12 Then

instead of

If item = Cells(u + 1, "AA").Value Then
Columns("aa:ad").NumberFormat = 0
Click to expand...
....

Format doesn't affect value. This is almost certainly the source of your
problem.
 
Hi,
I think it is because you are comparing "Item" which is variable with
Cells(u+1,"aa") which returns a range object. Therefore, try...

If item = Cells(u + 1, "aa").Value Then ....

Does this work?

OJ
 
I used your suggestion, ? Format(item - Cells(u + 1, "AA").Value,
"0.000E+000")
to check my values.
My result was:
0.000E+000

They appear to be the same. I also removed the line,
"Columns("aa:ad").NumberFormat = 0" and tried again. I got the same
result.

I used, "If Abs(CDbl(item) - Cells(u + 1, "AA").Value2) < 1E-12 Then"
THAT WORKED!

Although I do not understand what "Abs(CDbl(item)" means. Would by be
so kind as to explain what that does for me? I like to understand what
I am doing for next time.

Thank you very much.
 
CDbl() converts to a double precision value. Possibly item was declared
As Single?

Sub TryIt()
Dim x As Double, y As Single
x = 0.1
y = x
MsgBox x - y
End Sub

Abs() removes the sign of the difference. The difference 1-100 is
< 1E-12, but that doesen't mean that 1 and 100 are essentially equal.

Jerry
 
Back
Top