Look for Sheet name, and then skip...

[SteveDB1]

Morning all.
I have numerous workbooks with a series of named sheets, two or 3 of these
sheets has a specific name that I want to skip.
I.e.,
If sheets.name = "*APN*" or "*Sum*= true (where the asterisk denotes some
unknown value/variable)
then
skip/bypass/nextsheet/etc...
else
Run "mymacro"
how would I accomplish this?

Thank you.

 

[Rick Rothstein \(MVP - VB\)]

I'm not sure if you are running this in a loop or just processing one single
sheet, but sheets.name is not a valid reference. Assuming for this example
that WS is your worksheet name, this is the If-Then statement that you are
looking for...

If InStr(WS, "APN") > 0 Or InStr(WS, "Sum") > 0 Then

Note that as written, this test is case-sensitive (I assumed you wanted that
from the capitalizations used in your example). If you want the test to be
case-insensitive, use this instead....

If InStr(1, WS, "APN", vbTextCompare) > 0 Or _
InStr(1, WS, "Sum", vbTextCompare) > 0 Then

Rick

 

[Tom Ogilvy]

You can also use LIKE

demo from the immediate window
? Ucase("abcAPNefg") Like "*APN*"
True

 

[SteveDB1]

Hi Rick,
Yes, it's a looped routine, to work through all worksheets in a single
workbook, and then through multiple workbooks. But here I'm mainly interested
in how to work through all worksheets in a single workbook, for the purpose
of skipping a worksheet with a specific name.
Thank you for the response.
I'll go read up on the InStr() routine further.
I definitely think that the latter form--

If InStr(1, WS, "APN", vbTextCompare) > 0 Or _
InStr(1, WS, "Sum", vbTextCompare) > 0 Then

will work more for my purposes.
If I understand this correctly, the zero is acting as a boolean here,
correct?
Where 1 is true, and > 0 would also be true?

 

[SteveDB1]

hi Tom,
Thank you.
I'll examine LIKE as well.
I've found the Instr() function in the VBA help, and after reading it, I
believe it'll meet my exact need. Of course, trials begin next, so I'll go
from there.
Thank you.
Best.

 

[Tom Ogilvy]

Technically no. This is using it like a boolean where anything not zero is
true.
If InStr(1, WS, "APN", vbTextCompare) Or _
InStr(1, WS, "Sum", vbTextCompare) Then

Since Instr returns the location of the sub string within the string, and
zero if not found, then > 0 would indicate it was found. the result of the
comparison with >0 is a boolean result.

 

[Rick Rothstein \(MVP - VB\)]

No, the two logical expressions (comparisons) are generating the Boolean
values that are being Or'ed. The first logical expression is this...

InStr(1, WS, "APN", vbTextCompare) > 0

If the text string "APN" (with the individual letters in any case) exists
within the text string in WS, then the InStr function returns the position
of "APN" within WS; if that position is not zero (>0), then the logical
expression evaluates to True; if it isn't in WS, the InStr function returns
0 and the logical expression evaluates to False. The same thing happens in
the second logical expression so it also generates a True or False result.
Then the If-Then statement Or's them together to generate a final Boolean
value to act on.

Rick

 

[SteveDB1]

One more item.
The InStr() function tells me how to locate the string of interest to me,
but we missed something. Or, perhaps more accurately... I missed something.

Do I use an IF-true, then, Else-False structure to skip the worksheet?

It seems I would want a false statement to be the one I perform my macro on.
I.e., If-False, then, Else-True
So, I'd do something akin to:
if InStr(1,WS,"APN",vbtextCompare) = 0 or_
InStr(1,WS,"SUM",vbtextcompare) = 0

Run "MyMacro"

else

end if


Sorry. I just want to be clear on it.
strike it up to a muddled mind.....
Thanks.

 

[Rick Rothstein \(MVP - VB\)]

When you use an If-Then-Else structure, you have two blocks of code...

If <Logical Expression> Then
' code to run if true
Else
' code to run if false
End If

Just put your code in the appropriate sections... there is no requirement to
force the logical expression to return a True result in such a way that your
dominant code goes in the first code block... just put your code in
whichever code block makes sense given the logical expression's result. For
example, these two cases always produce the same result...

Case 1
========
If A = 0 Then
MsgBox "A is equal to zero."
Else
MsgBox "A does not equal zero."
End If

Case 2
========
If A <> 0 Then
MsgBox "A does not equal zero
Else
MsgBox .""A is equal to zero."
End If

To answer the question you were asking though, no, you can't simply change
the two logical tests and use the Or operator in both. This test...

If True Or True Then

is not the same as

If False Or False Then

which is what you tried to do. The proper "opposite" test to...

If True Or True Then

is this...

If False And False Then

But again, if you have constructed a logical test that makes sense to you
and works, but requires your dominant code to go in the Else section, then
just put your dominant code there.

Rick

 

[SteveDB1]

Hi Rick,
That was what I'd thought, yet for some reason my mind was working in two
different directions.
I'm glad it's wednesday.....
Again, thank you.