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

 

[Howard]

Hi Howard,



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






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

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
End Sub

Regards
Claus Busch

 

[Howard]

Hi Howard,



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






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

End Sub



Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

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

Thanks Claus.

 

[witek]

Hi Howard,

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


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]

AND not OR





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



De Morgan's law

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

Regards,
Howard

 

[Harald Staff]

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]

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

Have you read de Morgan's law ?

 

[witek]

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

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]

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]

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.