1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to add another "tab" to the formula?

Discussion in 'VBA Macros' started by Eloise T, Jul 17, 2017 at 1:11 AM.

  1. Eloise T

    Eloise T Active Member

    Messages:
    436
    I have the following line of code:

    If ws.Name <> "Formula Info" Then
    (Formula Info) is a tab in my workbook.

    I want to add the "Tech Rate" tab to the above formula line so it is an OR statement.

    Thus:

    If ws.Name <> "Formula Info" OR ws.Name <>"Tech Rate" Then
    If ws.Name <> "Formula Info" OR "Tech Rate" Then

    (I assume neither of the above two lines are correct...)
    Last edited: Jul 17, 2017 at 1:30 AM
  2. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,455
    You probably want
    If ws.Name <> "Formula Info" And ws.Name <>"Tech Rate" Then
  3. Eloise T

    Eloise T Active Member

    Messages:
    436
    So if I wanted to add even more tab names simply follow the same "formula", i.e. If ws.Name <> "Formula Info" And ws.Name <>"Tech Rate" And ws.Name <>"The Next one" Then etc.?
  4. YasserKhalil

    YasserKhalil Active Member

    Messages:
    603
    Hello Eloise
    Yes that's right. Your syntax is correct now
  5. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,455
    Yes,
    But depending on the structure/name of the tabs you may be able to simplify it like right(ws.Name,3)<>"day" will exclude all tabs named after a day
    Chirag R Raval likes this.
  6. Eloise T

    Eloise T Active Member

    Messages:
    436
    Thank you!
  7. Eloise T

    Eloise T Active Member

    Messages:
    436
    Thank you!
  8. Eloise T

    Eloise T Active Member

    Messages:
    436
    What if it's an AND situation? ...in other words, I want both (or multiple) tabs to be true?
  9. YasserKhalil

    YasserKhalil Active Member

    Messages:
    603
    Hello
    When saying : If ws.Name <> "Formula Info" And ws.Name <>"Tech Rate" Then
    This means that these two sheets will be excluded and if you need to exclude more sheets, you would follow the same syntax .. That's to put AND then ws.Name <> "AnySheetName" ..
  10. Eloise T

    Eloise T Active Member

    Messages:
    436
    I think I get it. I was overlooking the <>.
    So it's:
    IF (x is true) AND (y is true) THEN vs.
    IF (x is true and y is true) THEN

    Correct?
  11. YasserKhalil

    YasserKhalil Active Member

    Messages:
    603
    Yes that's right ..
    x=True AND y=True Then the final output =True
    AND means that all the conditions must be achieved so all the conditions must be TRUE to have the final result TRUE
  12. Eloise T

    Eloise T Active Member

    Messages:
    436
    So how would the code change if it were an OR situation, or do you not use OR in VBA?
  13. Eloise T

    Eloise T Active Member

    Messages:
    436
    Also, can just a line or two of code be folded in to one of the other sub routines so that there are only three sub routines?
  14. Monty

    Monty Well-Known Member

    Messages:
    758
    Eloise T

    Can you please upload your file.
  15. YasserKhalil

    YasserKhalil Active Member

    Messages:
    603
    If you used OR in this case, there will be confusion as if any of the criteria was True then the statement will be True and execute the following lines
    Example :
    Code (vb):
    If ws.Name <> "Sheet1" And ws.Name <> "Sheet2" Then
    In this line the following lines will be executed only when ws.name doesn't equal to "Sheet1" and at the same time ws.name doesn't equal to "Sheet2" (and this is important for your code because you want to exclude both of them)

    But if you used OR

    Code (vb):
    If ws.Name <> "Sheet1" Or ws.Name <> "Sheet2" Then
    if the loop was on Sheet1, then in this case the first part result =False but the second part will be True .. and because of using OR (the statement will be True)
    False Or True = True
    In that case the Sheet1 will not be excluded from the loop and I think that is not desired at all

    Have a look at this link
    https://www.tutorialspoint.com/vba/vba_operators.htm

    Hope it is clear now

Share This Page