+ Reply to Thread
Results 1 to 5 of 5

Use of If...Then...AND

  1. #1
    Registered User
    Join Date
    04-29-2014
    Location
    Honlulu, HI
    MS-Off Ver
    Excel 2010
    Posts
    5

    Use of If...Then...AND

    Gurus,

    I understand that logical expressions such as AND, OR can be used with IF...THEN such as

    Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer

    Please Login or Register  to view this content.
    i.e. IF...AND...THEN


    BUT...is it possible to meet the criteria of the IF, then perform two or more operations following the THEN if this single criteria is met?

    i.e. IF...THEN...AND

    One caveat...the initial criteria is deleted.
    I want to search Column A for the word "Member" and then delete the row and all of the subsequent blank rows that follow because they contain member related information.

    Please Login or Register  to view this content.

    Spreadsheet looks like this:

    Member Name Address
    xxx yyy
    xxx yyy
    Hopeful Name Address
    aaa bbb
    aaa bbb
    Hopeful Name Address
    aaa bbb

    (Note "xxx" and "aaa" are in column Name and "yyy" and "bbb" are in column Address.)
    This an extract from another program with contains hundreds of entries.

    I understand the importance of working backwards when deleting, but I need to delete based on "Member" criteria. Working backwards seems...wow!

    Or is there just a better way?
    Thank you!!!
    Last edited by 6StringJazzer; 06-27-2014 at 02:55 PM.

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Use of If...Then...AND

    Both of your code samples have syntax errors.
    #1. If Range("A" & i) = "Member" Then Rows(i).Delete
    End If

    Since you have included a statement after the Then, the "End If" is in error (End If without Block If) and will not compile.

    Choose either:
    Please Login or Register  to view this content.
    or
    Please Login or Register  to view this content.


    In the second case, the the If line ends with "Then". This is the block form of the IF statement and requires an "End If"
    This

    Please Login or Register  to view this content.
    will error with: Block If without End If

    It must be:

    Please Login or Register  to view this content.


    "... is it possible to meet the criteria of the IF, then perform two or more operations following the THEN if this single criteria is met? "
    Yes. It requires the BLOCK IF form: each line after Then and above the End If is actually an "And" statement
    Please Login or Register  to view this content.
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    04-29-2014
    Location
    Honlulu, HI
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Use of If...Then...AND

    To all:
    I apologize...I am sure that the Moderator was doing his job by designating the "code" I included as code; however, those lines were just put in to provide an example of what I was talking about. Those lines were NOT my code! By him doing so, he only created confusion vice clarity, thus the reason I did not designate the line as my code.

    Thanks protoLeah, I believe you addressed my question in your last entry above.

  4. #4
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Use of If...Then...AND

    Hello rdtrahan1906,

    I am sure that the Moderator was doing his job by designating the "code" I included as code; however, those lines were just put in to provide an example of what I was talking about. Those lines were NOT my code! By him doing so, he only created confusion vice clarity
    Any snippet, or line extracted from code, should, according to Forum Rules, be posted with the Code Tags, as pointed out by the Moderator.

    Regards.
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .

  5. #5
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Use of If...Then...AND

    Quote Originally Posted by rdtrahan1906 View Post
    ...those lines were just put in to provide an example of what I was talking about. Those lines were NOT my code! By him doing so, he only created confusion vice clarity, thus the reason I did not designate the line as my code.
    The lines had every appearance of code, including indentation. The reason we require code tags is to preserve the formatting of the code, thus improving readability. Without code tags, your indentation was lost.

    If you wish to post something that looks like code but is not intended to be compilable code, then it should include a note to that effect, and also include code tags for readability.

    _______________________

    If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved.

    Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

    If a member helped you solve your problem, consider adding to their reputation by clicking on the star icon below their name.

+ 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