+ Reply to Thread
Results 1 to 8 of 8

My If statement results in False when actually true

  1. #1
    papa jonah
    Guest

    My If statement results in False when 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.



    Expand AllCollapse All


  2. #2
    Don Guillett
    Guest

    Re: My If statement results in False when actually true

    Maybe a rounding error.

    --
    Don Guillett
    SalesAid Software
    [email protected]
    "papa jonah" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    >




  3. #3
    papa jonah
    Guest

    Re: My If statement results in False when actually true

    The 1's are not results of calculations.


  4. #4
    Valued Forum Contributor mudraker's Avatar
    Join Date
    11-10-2003
    Location
    Melbourne, Australia
    Posts
    3,983
    I have seen similar problem when one item is treated as text and the other is treated as numeric

  5. #5
    Harlan Grove
    Guest

    Re: My If statement results in False when actually true

    "papa jonah" <[email protected]> wrote...
    >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.


    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

    ....

    Format doesn't affect value. This is almost certainly the source of your
    problem.



  6. #6
    OJ
    Guest

    Re: My If statement results in False when actually true

    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


  7. #7
    papa jonah
    Guest

    Re: My If statement results in False when actually true

    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.


  8. #8
    Jerry W. Lewis
    Guest

    Re: My If statement results in False when actually true

    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

    papa jonah wrote:

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



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1