If resulting in False when it is actually true

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.

HI~

Maybe..

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


Goodluck


"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.

that's not it

"배병ëÂ*¬" wrote:

> HI~
>
> Maybe..
>
> cells(u+1,"aa") --> Range("aa" & u+1)
>
>
> Goodluck
>
>
> "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.

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

"Papa Jonah" <PapaJonah@discussions.microsoft.com> wrote in message
news:7BEC29B1-65D0-42EE-87C2-FAD2B06B6685@microsoft.com...
> 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.

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

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.

"OJ" wrote:

> 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
>
>

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.

--
Regards,
Tom Ogilvy

"Papa Jonah" <PapaJonah@discussions.microsoft.com> wrote in message
news:F138CE41-6733-413B-98F4-37D814DE8B69@microsoft.com...
> 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.
>
> "OJ" wrote:
>
> > 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
> >
> >

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


"NickHK" wrote:

> 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
>
> "Papa Jonah" <PapaJonah@discussions.microsoft.com> wrote in message
> news:7BEC29B1-65D0-42EE-87C2-FAD2B06B6685@microsoft.com...
> > 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.
>
>
>

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

"Papa Jonah" <PapaJonah@discussions.microsoft.com> wrote in message
news:5610E3F2-B853-4CD2-B7E0-B2B6FAF431BF@microsoft.com...
> 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
>
>
> "NickHK" wrote:
>
> > 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
> >
> > "Papa Jonah" <PapaJonah@discussions.microsoft.com> wrote in message
> > news:7BEC29B1-65D0-42EE-87C2-FAD2B06B6685@microsoft.com...
> > > 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.
> >
> >
> >