A '.Select' question

[Stuart]

I have recently restructured code in a routine and within the
new code, now have added a check for sheets to exclude:

For Each ws In ActiveWorkbook.Worksheets
If Not (ws.Name Like "DataStore" Or ws.Name Like "*Architect's
Instructions*" Or _
ws.Name Like "*Variations*" Or ws.Name Like "*Materials*" Or ws.Name
Like _
"*Preliminaries*" Or ws.Name Like "*Summary*" Or ws.Name Like _
"*SUMMARY*" Or ws.Name Like "*MASTER*") Then
With ws
'Find which type of BofQ we are dealing with, and in which column the
'£'
'is found. NB: This routine assumes that '£' will always occur in the
last
'but one column
' .Select
For Each C In Range("A1:Z100")
If C.Value = SEARCHFOR Then
£Col = C.Column
Exit For
End If
Next
End With
End If
Next

After adding the exclude sheet code, I found that £Col was equal to 0, even
when it should have been a different value, and even when Locals showed 'ws'
working with a 'valid' sheet.

So I added ...'.Select'

Now it 'appears' to work. ............why is this, please?

Regards.
(unqualified..again?)

 

[steve]

Stuart,

My guess is that

For Each C In Range("A1:Z100")
If C.Value = SEARCHFOR Then
£Col = C.Column
Exit For
End If
Next

isn't finding anything...

Is SEARCHFOR a variable or a text string?
If it is a text string than it should be "SEARCHFOR" (in quotes).

 

[Stuart]

Apologies for the linewrap.
SEARCHFOR is dimmed as follows:
Const SEARCHFOR As Variant = "£"

Thanks and Regards.

 

[Tom Ogilvy]

For Each ws In ActiveWorkbook.Worksheets
If Not (ws.Name Like "DataStore" Or ws.Name Like "*Architect's
Instructions*" Or _
ws.Name Like "*Variations*" Or ws.Name Like "*Materials*" Or ws.Name
Like _
"*Preliminaries*" Or ws.Name Like "*Summary*" Or ws.Name Like _
"*SUMMARY*" Or ws.Name Like "*MASTER*") Then
With ws
'Find which type of BofQ we are dealing with, and in which column the
'£'
'is found. NB: This routine assumes that '£' will always occur in the
last
'but one column
' .Select
For Each C In Range("A1:Z100") '<== ther is no period here
' so this refers to the active sheet - your WITH is doing nothing
' change to .Range("A1:Z100") and you won't need to select
If C.Value = SEARCHFOR Then
£Col = C.Column
Exit For
End If
Next
End With
End If
Next

 

[Stuart]

Missed it.

Regards and thanks.

For Each ws In ActiveWorkbook.Worksheets
If Not (ws.Name Like "DataStore" Or ws.Name Like "*Architect's
Instructions*" Or _
ws.Name Like "*Variations*" Or ws.Name Like "*Materials*" Or ws.Name
Like _
"*Preliminaries*" Or ws.Name Like "*Summary*" Or ws.Name Like _
"*SUMMARY*" Or ws.Name Like "*MASTER*") Then
With ws
'Find which type of BofQ we are dealing with, and in which column the
'£'
'is found. NB: This routine assumes that '£' will always occur in the
last
'but one column
' .Select
For Each C In Range("A1:Z100") '<== ther is no period here
' so this refers to the active sheet - your WITH is doing nothing
' change to .Range("A1:Z100") and you won't need to select
If C.Value = SEARCHFOR Then
£Col = C.Column
Exit For
End If
Next
End With
End If
Next