Loop to skip some sheets in workbook

[Howard]

What do I put where it says 'DO NOTHING so the loop will skip ws. X, Y & Z?

Thanks,
Howard

Option Explicit

Sub LoopThroughSheets()
Dim ws As Worksheet
If ws.Name = "X" Or ws.Name = "Y" Or ws.Name = "Z" Then
'DO NOTHING
Else
'Do some code stuff here like:
ws.Range("B1") = ws.Name
End If
End Sub

[Claus Busch]

Hi Howard,

Am Wed, 22 May 2013 23:24:34 -0700 (PDT) schrieb Howard:

> What do I put where it says 'DO NOTHING so the loop will skip ws. X, Y & Z?

Sub LoopThroughSheets()
Dim ws As Worksheet
If ws.Name = "X" Or ws.Name = "Y" Or ws.Name = "Z" Then

Else
'Do some code stuff here like:
ws.Range("B1") = ws.Name
End If
End Sub

or:

Sub LoopThroughSheets()
Dim ws As Worksheet

If ws.Name <> "X" Or ws.Name <> "Y" Or ws.Name <> "Z" Then
''Do some code stuff here like:

ws.Range("B1") = ws.Name
End If
End Sub

Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

[Howard]

Thanks, Claus. As always you make it look so easy.

Regards,
Howard

[Howard]

> Thanks, Claus. As always you make it look so easy.
>
>
>
> Regards,
>
> Howard

Spoke too soon.

Errors with Object variable or with block variable not set.

Howard

[Claus Busch]

Hi Howard,

Am Thu, 23 May 2013 01:33:04 -0700 (PDT) schrieb Howard:

> Errors with Object variable or with block variable not set.

the loop is missing:

Sub LoopThroughSheets()
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

If ws.Name = "X" Or ws.Name = "Y" Or ws.Name = "Z" Then
Else
'Do some code stuff here like:
ws.Range("B1") = ws.Name
End If

Next

[Howard]

I was looking right it and never saw that, it's a bit late but really my bad.

Thanks Claus.

[witek]

Claus Busch wrote:
> Hi Howard,
>
> Am Wed, 22 May 2013 23:24:34 -0700 (PDT) schrieb Howard:
>
>> What do I put where it says 'DO NOTHING so the loop will skip ws. X, Y & Z?
>
> Sub LoopThroughSheets()
> Dim ws As Worksheet
> If ws.Name = "X" Or ws.Name = "Y" Or ws.Name = "Z" Then
> Else
> 'Do some code stuff here like:
> ws.Range("B1") = ws.Name
> End If
> End Sub
>
> or:
>
> Sub LoopThroughSheets()
> Dim ws As Worksheet
> If ws.Name <> "X" Or ws.Name <> "Y" Or ws.Name <> "Z" Then
> ''Do some code stuff here like:
> ws.Range("B1") = ws.Name
> End If
> End Sub
>
>
> Regards
> Claus Busch
>

AND not OR


If ws.Name <> "X" AND ws.Name <> "Y" AND ws.Name <> "Z"

De Morgan's law

[Howard]

Thanks, witek, brings the second code around quite nicely.

Regards,
Howard

[Harald Staff]

"witek" <wite...@gazeta.pl.invalid> skrev i melding
news:knkune$kvq$4...@dont-email.me...

>> If ws.Name = "X" Or ws.Name = "Y" Or ws.Name = "Z" Then
>

> AND not OR
>
> If ws.Name <> "X" AND ws.Name <> "Y" AND ws.Name <> "Z"
>

No. A ws can not have three names at the same time so that test will always
fail.

[witek]

Have you read de Morgan's law ?

[witek]

try what is value of ws.Name <> "X" AND ws.Name <> "Y" AND ws.Name <>
"Z" if ws.Name = "T" .

Is that really false?

please believe me. It is correct.

[Howard]

>
> try what is value of ws.Name <> "X" AND ws.Name <> "Y" AND ws.Name <>
>
> "Z" if ws.Name = "T" .
>
>
>
> Is that really false?
>
>
>
> please believe me. It is correct.

I have zero expertise to discuss de Morgan's law, these two macros work on my workbook which has Sheet1, Sheet2, Sheet3, three sheets named X, Y, Z (not SheetX, SheetY, SheetZ)and Sheet4. In that order. The macros are in Sheet1 module.

Option Explicit

Sub LoopThroughSheets()
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

If ws.Name = "X" Or ws.Name = "Y" Or ws.Name = "Z" Then

Else
'Do your code stuff here like:
With ws.Cells
With .Font
.ColorIndex = 3
.Bold = False
.Italic = False
.Underline = False
.Name = "Arial"
.Size = 12
End With
End With

End If

Next
End Sub

Sub LoopThroughSheetsXX()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

If ws.Name <> "X" And ws.Name <> "Y" And ws.Name <> "Z" Then
ws.Range("C5") = ws.Name
With ws.Cells
With .Font
.ColorIndex = 0
.Bold = True
.Italic = True
.Underline = True
.Name = "Arial"
.Size = 12
End With
End With

End If
Next

End Sub

Regards,
Howard

[Harald Staff]

"witek" <wite...@gazeta.pl.invalid> skrev i melding

news:knln2e$kso$3...@dont-email.me...

> Harald Staff wrote:
>> "witek" <wite...@gazeta.pl.invalid> skrev i melding
>> news:knkune$kvq$4...@dont-email.me...
>>>> If ws.Name = "X" Or ws.Name = "Y" Or ws.Name = "Z" Then
>>>
>>> AND not OR
>>>
>>> If ws.Name <> "X" AND ws.Name <> "Y" AND ws.Name <> "Z"
>>>
>>
>> No. A ws can not have three names at the same time so that test will
>> always
>> fail.

> try what is value of ws.Name <> "X" AND ws.Name <> "Y" AND ws.Name <> "Z"
> if ws.Name = "T" .
>

Sorry, you're right. It stated "AND not OR", but not "<> not =" , so I
misread it. But it leaves the question why this? Does it perform better than
the original, or than a Select Case, or three If's ?

[witek]

Harald Staff wrote:
> "witek" <wite...@gazeta.pl.invalid> skrev i melding
> news:knln2e$kso$3...@dont-email.me...
>> Harald Staff wrote:
>>> "witek" <wite...@gazeta.pl.invalid> skrev i melding
>>> news:knkune$kvq$4...@dont-email.me...
>>>>> If ws.Name = "X" Or ws.Name = "Y" Or ws.Name = "Z" Then
>>>>
>>>> AND not OR
>>>>
>>>> If ws.Name <> "X" AND ws.Name <> "Y" AND ws.Name <> "Z"
>>>>
>>>
>>> No. A ws can not have three names at the same time so that test will
>>> always
>>> fail.
>
>> try what is value of ws.Name <> "X" AND ws.Name <> "Y" AND ws.Name <> "Z"
>> if ws.Name = "T" .
>>
>
> Sorry, you're right. It stated "AND not OR", but not "<> not =" , so I
> misread it. But it leaves the question why this? Does it perform better than
> the original, or than a Select Case, or three If's ?
>
>

no difference between ifs.
simply if with empty then clause does not look good.
select case can be a little bit faster because once case is found it
jumps out of select case statement.