• 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...

  • 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?

Eloise T

Active Member
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:
You probably want
If ws.Name <> "Formula Info" And ws.Name <>"Tech Rate" Then
 
You probably want
If ws.Name <> "Formula Info" And ws.Name <>"Tech Rate" Then
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.?
 
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
 
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
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

Thank you!
 
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" ..
 
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" ..
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?
 
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
 
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
So how would the code change if it were an OR situation, or do you not use OR in VBA?
 
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

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?
 
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:
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:
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
 
Eloise T

Can you please upload your file.

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?
 

Attachments

  • Chandoo - sample date fix.xlsx
    201.5 KB · Views: 0
Last edited:
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
 
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:
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:
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
Thank you for the link and your assistance!
 
Back
Top