Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Formula Help (2000)

    Hi all,

    Could someone take a look at this and tell me if they see anything wrong with the structure of this If Statement?
    If Sheets("logs").Cells(5, 6).Value = " A" And _
    Sheets("logs").Cells(5, 9).Value = "UNIT 1" And _
    Sheets("logs").Cells(5, 10).Value = "Couple 1" Then
    Sheets("charts").Cells(4, 25).Value = Sheets("charts").Cells(4, 25).Value + 1
    End If
    If Sheets("logs").Cells(5, 6).Value = " A" And _
    Sheets("logs").Cells(5, 9).Value = "Unit 1" And _
    Sheets("logs").Cells(5, 10).Value = "Couple 1" And _
    Sheets("logs").Cells(5, 12).Value = "FAR" Then
    Sheets("charts").Cells(5, 25).Value = Sheets("charts").Cells(5, 25).Value + 1
    End If
    If Sheets("logs").Cells(5, 6).Value = " A" And _
    Sheets("logs").Cells(5, 9).Value = "Unit 1" And _
    Sheets("logs").Cells(5, 10).Value = "Couple 1" And _
    Sheets("logs").Cells(5, 12).Value = "FAR Center" Then
    Sheets("charts").Cells(6, 25).Value = Sheets("charts").Cells(6, 25).Value + 1
    End If
    If Sheets("logs").Cells(5, 6).Value = " A" And _
    Sheets("logs").Cells(5, 9).Value = "Unit 1" And _
    Sheets("logs").Cells(5, 10).Value = "Couple 1" And _
    Sheets("logs").Cells(5, 12).Value = "NEAR Center" Then
    Sheets("charts").Cells(7, 25).Value = Sheets("charts").Cells(7, 25).Value + 1
    End If
    If Sheets("logs").Cells(5, 6).Value = " A" And _
    Sheets("logs").Cells(5, 9).Value = "Unit 1" And _
    Sheets("logs").Cells(5, 10).Value = "Couple 1" And _
    Sheets("logs").Cells(5, 12).Value = "NEAR" Then
    Sheets("charts").Cells(8, 25).Value = Sheets("charts").Cells(8, 25).Value + 1
    End If

    My problem with this is that I it doesn't see the value in the cells, and thus doesn't give add 1

    Thanks,
    Darryl.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Help (2000)

    One possible thing that I see is that the first If statement has this:

    <pre> Sheets("logs").Cells(5, 9).Value = "UNIT 1"
    </pre>


    and all the rest have this:

    <pre> Sheets("logs").Cells(5, 9).Value = "Unit 1"
    </pre>


    The difference in Upper/Lower case letters in the word "Unit" could be causing you problems.

    If that is not your problem, could you upload a workbook that shows the problem and explain what you expect to happen when you run the macro.

    One other question. Is there a reason that you are using:

    <pre> Sheets("logs").Cells(5, 9).Value
    </pre>


    rather than the much easier to read:

    <pre>Sheets("logs").Range("I5").Value
    </pre>


    I also think that those If statements would be easier to read and more efficient if they were written like this, which I think does the same thing:

    <pre> If Sheets("logs").Range("F5").Value = " A" And _
    Sheets("logs").Range("I5").Value = "UNIT 1" And _
    Sheets("logs").Range("J5").Value = "Couple 1" Then
    Sheets("charts").Range("Y4").Value = Sheets("charts").Range("Y4").Value + 1
    Select Case Sheets("logs").Range("L5").Value
    Case "FAR"
    Sheets("charts").Range("Y5").Value = Sheets("charts").Range("Y5").Value + 1
    Case "FAR Center"
    Sheets("charts").Range("Y6").Value = Sheets("charts").Range("Y6").Value + 1
    Case "NEAR Center"
    Sheets("charts").Range("Y7").Value = Sheets("charts").Range("Y7").Value + 1
    Case "NEAR"
    Sheets("charts").Range("Y8").Value = Sheets("charts").Range("Y8").Value + 1
    End Select
    End If
    </pre>

    Legare Coleman

  3. #3
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Formula Help (2000)

    Thanks Legare,

    I tried everything to figure out why it wasn't working, I thought it might be the structure of the statement, I didn't think about upper/lower case, it now works. <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

    I am going to try the select case,

    Thanks

    Darryl.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formula Help (2000)

    If case is an issue, you can convert to Upper case before you compare:
    <pre> If UCase(Sheets("logs").Range("F5").Value) = " A" And _
    UCase(Sheets("logs").Range("I5").Value) = "UNIT 1" And _
    UCase(Sheets("logs").Range("J5").Value = "COUPLE 1" Then</pre>


    Steve

  5. #5
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Formula Help (2000)

    Question,

    forgive me if I'm wrong, IS Ucase considered a variant? And with the use of that function does it hold that value in memory? so if it were an "a" it would read it as "A" am I correct, and the same for Lcase?

    Thanks,
    Darryl.

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Help (2000)

    UCase and LCase are functions that returns a variant(string) with all lower case letters converted to upper case and all other characters left unchanged.
    Legare Coleman

Posting Permissions

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