Loop to skip some sheets in workbook
 

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

Loop to skip some sheets in workbook
 

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

Loop to skip some sheets in workbook
 

On Thursday, May 23, 2013 1:08:18 AM UTC-7, 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

--

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

Loop to skip some sheets in workbook
 

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

Loop to skip some sheets in workbook
 

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Loop to skip some sheets in workbook
 

On Thursday, May 23, 2013 1:37:23 AM UTC-7, Claus Busch wrote:
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

--

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.

Loop to skip some sheets in workbook
 

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

Loop to skip some sheets in workbook
 

On Thursday, May 23, 2013 4:36:05 AM UTC-7, witek wrote:
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

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

Regards,
Howard

Loop to skip some sheets in workbook
 

"witek" skrev i melding
...
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.

Loop to skip some sheets in workbook
 

Harald Staff wrote:
"witek" skrev i melding
...
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.




Have you read de Morgan's law ?

Loop to skip some sheets in workbook
 

Harald Staff wrote:
"witek" skrev i melding
...
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" .

Is that really false?

please believe me. It is correct.

Loop to skip some sheets in workbook
 

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

Loop to skip some sheets in workbook
 

"witek" skrev i melding
...
Harald Staff wrote:
"witek" skrev i melding
...
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 ?

Loop to skip some sheets in workbook
 

Harald Staff wrote:
"witek" skrev i melding
...
Harald Staff wrote:
"witek" skrev i melding
...
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.