Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    True/False Statements (Excel XP)

    I don't understand something here with this statement:

    If InStr(LCase(Range("P17")), "") = 1 Then

    It would seem to me that the "=1" means that if the statement is TRUE then the subsequent proceedure will take place. However, what I'm always getting is that it takes place if the statement is FALSE.

    In other workds what is taking place is: if P17 is empty, then "=1" is not running the subsequent proceedure and if I have "=0" then it does run the subsequent proceedure.

    It would seem to me it should be the other way around.???

    Thanks,
    BH

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: True/False Statements (Excel XP)

    If you want to test whether cell P17 is blank, use

    If Range("P17") = "" Then

    The InStr function doesn't return True or False, but 0 if the search string is not found, and the position of its first occurrence if found. It is not intended to be used with an empty string "" as search string - it doesn't make sense to search for an empty string in another string.

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: True/False Statements (Excel XP)

    It depends what you think this statement does: <code>InStr(LCase(Range("P17")), "") </code>
    That will return 0 if the value of P17 is zero length, 1 otherwise.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: True/False Statements (Excel XP)

    Thanks guys. As usual a limited amount of knowledge leads to more complex (and erroneous) solutions!

    BH

  5. #5
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: True/False Statements (Excel XP)

    Okay, if

    If Range("O17") = "" Then

    processes the Then statement,

    how do I make it process the Then statement when cell O17 contains any text......or that is, when O17 "is not equal to being empty. I want to process a group of operations only if someing IS in O17 as versus doing so when O17 is empty.

    Thanks,
    BH

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: True/False Statements (Excel XP)

    Either

    If Not Range("O17") = "" Then

    or

    If Range("O17") <> "" Then

    Note: if you receive this reply by e-mail, the second line may not be rendered correctly, but it should be OK on the website itself.

  7. #7
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: True/False Statements (Excel XP)

    Thanks Hans. I've started a small library of these very basic and useful command formulas you've been giving me. It should help in the future to come up with the correct code in similar situations.

    BH

Posting Permissions

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