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?

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

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

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

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

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.

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

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

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

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 ?

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.

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

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