If resulting in False when it is actually true

[Guest]

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.

 

[Guest]

HI~

Maybe..

cells(u+1,"aa") --> Range("aa" & u+1)


Goodluck


"Papa Jonah"ë‹˜ì´ ìž‘ì„±í•œ ë‚´ìš©:

 

[Guest]

that's not it

HI~

Maybe..

cells(u+1,"aa") --> Range("aa" & u+1)


Goodluck


"Papa Jonah"ë‹˜ì´ ìž‘ì„±í•œ ë‚´ìš©:

 

[NickHK]

Papa Jonah,
Are you sure your NumberFormat on aa:ad is being applied ?
Check help where it says this should take a string.
So I would guess you end up comparing a string and a number; hence False.

NickHK

 

[OJ]

Hi Jonah,
try ...
If item = Cells(u + 1, "aa").Value

I think Cells(u+1,"aa") returns a range object..From Help...

Using this property without an object qualifier returns a Range object
that represents all the cells on the active worksheet.

Hth,
OJ

 

[Guest]

NickHK,
I am not sure I understand your statement, "Check help where it says this
should take a string."
The code that I have includes a line, "Columns("aa:ad").NumberFormat = 0".

I also suspected the number vs text idea. However, I modeled this code
after another section that works fine. But even so, other than the
numberformat line, I do not know how to ensure that I am comparing numbers to
numbers and not text.

Can you clarify your statement for me. Thanks.
Papa J

 

[Guest]

OJ,
I have tried adding .value. That did not work either.
This section of code was modeled after another section that does work. The
only difference is I don't think it involved numbers, but text.

 

[NickHK]

Papa J
If you use the macro recorder, you will get code like:
Columns("aa:ad").NumberFormat = "0"
instead of your current:
Columns("aa:ad").NumberFormat = 0

See the difference ?

However, I see that Excel does not update the data type of the value until
you edit it, so I feel even with this correction, it will still return
False.
If this proves true, you could use one of the coersion functions (CInt,
CLng, CDbl) or Val etc to be sure you are actually comparing numbers.
If floating point values are involved, decide what level tolerance you
require.

NickHK

 

[Tom Ogilvy]

It does return a range object, but the default propety of a range object is
value, so this suggestion would not affect the results in this situation as
you have discovered. -- just for clarification.