Loop to skip some sheets in workbook

  • Thread starter Thread starter Howard
  • Start date Start date
H

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
 
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?
Click to expand...

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
 
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
Click to expand...

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

Regards,
Howard
 
Hi Howard,

Am Thu, 23 May 2013 01:33:04 -0700 (PDT) schrieb Howard:
Errors with Object variable or with block variable not set.
Click to expand...

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
 
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
Click to expand...

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

Thanks Claus.
 
Claus said:
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
Click to expand...


AND not OR


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

De Morgan's law
 
witek said:
AND not OR

If ws.Name <> "X" AND ws.Name <> "Y" AND ws.Name <> "Z"
Click to expand...

No. A ws can not have three names at the same time so that test will always
fail.
 
Harald said:
No. A ws can not have three names at the same time so that test will always
fail.
Click to expand...


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.
 
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.
Click to expand...


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
 
try what is value of ws.Name <> "X" AND ws.Name <> "Y" AND ws.Name <> "Z"
if ws.Name = "T" .
Click to expand...

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 ?
 
Harald said:
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 ?
Click to expand...

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.
 
Back
Top