| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look for Sheet name, and then skip...
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Look for Sheet name, and then skip...
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 "SteveDB1" wrote in message ... 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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Look for Sheet name, and then skip...
You can also use LIKE
demo from the immediate window ? Ucase("abcAPNefg") Like "*APN*" True -- Regards, Tom Ogilvy "SteveDB1" wrote: 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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Look for Sheet name, and then skip...
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? "Rick Rothstein (MVP - VB)" wrote: 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 "SteveDB1" wrote in message ... 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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Look for Sheet name, and then skip...
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" wrote: You can also use LIKE demo from the immediate window ? Ucase("abcAPNefg") Like "*APN*" True -- Regards, Tom Ogilvy "SteveDB1" wrote: 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. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Look for Sheet name, and then skip...
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. -- Regards, Tom Ogilvy "SteveDB1" wrote: 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? "Rick Rothstein (MVP - VB)" wrote: 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 "SteveDB1" wrote in message ... 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. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Look for Sheet name, and then skip...
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" wrote in message ... 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? "Rick Rothstein (MVP - VB)" wrote: 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 "SteveDB1" wrote in message ... 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. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Look for Sheet name, and then skip...
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)" wrote: 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" wrote in message ... 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? "Rick Rothstein (MVP - VB)" wrote: 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 "SteveDB1" wrote in message ... 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. |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Look for Sheet name, and then skip...
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" wrote in message ... 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)" wrote: 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" wrote in message ... 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? "Rick Rothstein (MVP - VB)" wrote: 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 "SteveDB1" wrote in message ... 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. |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Look for Sheet name, and then skip...
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. "Rick Rothstein (MVP - VB)" wrote: 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" wrote in message ... 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)" wrote: 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" wrote in message ... 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? "Rick Rothstein (MVP - VB)" wrote: 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 "SteveDB1" wrote in message ... 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| copy from one sheet to another and skip the blank rows | Excel Discussion (Misc queries) | |||
| paste data in different sheet but skip rows. | Excel Discussion (Misc queries) | |||
| using IF to skip | Excel Worksheet Functions | |||
| skip to next from if | Excel Programming | |||
| Skip function if data sheet is empty | Excel Programming | |||
Linear Mode
