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.

  1. Eloise T

    Eloise T Active Member

    Messages:
    636
    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
  2. Hui

    Hui Excel Ninja Staff Member

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

    Eloise T Active Member

    Messages:
    636
    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:
    719
    Hello Eloise
    Yes that's right. Your syntax is correct now
  5. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,619
    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:
    636
    Thank you!
  7. Eloise T

    Eloise T Active Member

    Messages:
    636
    Thank you!
  8. Eloise T

    Eloise T Active Member

    Messages:
    636
    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:
    719
    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:
    636
    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:
    719
    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:
    636
    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:
    636
    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:
    833
    Eloise T

    Can you please upload your file.
  15. YasserKhalil

    YasserKhalil Active Member

    Messages:
    719
    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
  16. Eloise T

    Eloise T Active Member

    Messages:
    636
    The original problem was to have a VGA change the dates in Column G from whatever format to: mm/dd/yyyy

    There are multiple tabs in the workbook all (all but the 2 listed) with dates in Column G. ...and therein lies this problem. I didn't want tab labeled "Formula Info" to be acted upon by the VBA since it had no dates in Column G since it was the Formula Info tab. (duh added for humor)

    When I wanted to add another "non-dated Column G" tab, I needed to know how to simply add to the list of tab for the VGA to avoid, hence the modification:

    If ws.Name <> "Formula Info" And ws.Name <> "any other name" Then

    I was confused as to why it was not "Or" instead of "And" because both did not need to be true; however, both would be true. Confused yet?

    Questions?

    Attached Files:

    Last edited: Jul 21, 2017
  17. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,403
    Hi ,

    The reason for using the AND operator is because the test is for inequality.

    Suppose you wish to test for equality ; the test would then be :

    If a sheet name is equal to name1 OR a sheet name is equal to name2

    Suppose we have three sheets named name1 , name2 and name3.

    For the tab named name1 , the above IF statement would return a TRUE result , since this name satisfies the first test , and since an OR statement returns TRUE if any of its components is TRUE , the IF statement will return TRUE.

    For the tab named name2 , the above IF statement would return a TRUE result , since this name satisfies the second test.

    For the tab named name3 , the above IF statement would return a FALSE result , since this name satisfies neither the first test nor the second test.

    Thus , when testing for equality , generally , an OR combination is used.

    When you test for inequality , the test is as follows :

    If a sheet name is not equal to name1 AND a sheet name is not equal to name2.

    With the same three sheets , for the tab named name1 , the above IF statement would return a FALSE result , since this name does not satisfy the first test , and since an AND statement returns FALSE if any of its components is FALSE , the IF statement will return FALSE.

    For the tab named name2 , again the above IF statement would return a FALSE result , since this name satisfies the first test but does not satisfy the second test.

    For the tab named name3 , the above IF statement would return a TRUE result , since this name satisfies the first test (it is not equal to name1) , and it satisfies the second test (it is not equal to name2) and since an AND statement returns TRUE if all of its components are TRUE , the IF statement will return TRUE.

    Thus , generally when testing for inequality , we use the AND operator.

    Narayan
  18. Eloise T

    Eloise T Active Member

    Messages:
    636
    Thank you for the link and your assistance!

Share This Page