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



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.528 / Virus Database: 324 - Release Date: 16/10/2003

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

--
sb
"Stuart" wrote in message
...
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?)



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.528 / Virus Database: 324 - Release Date: 16/10/2003

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

Thanks and Regards.

"steve" wrote in message
...
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).

--
sb
"Stuart" wrote in message
...
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?)



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.528 / Virus Database: 324 - Release Date: 16/10/2003






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.528 / Virus Database: 324 - Release Date: 16/10/2003

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


--
Regards,
Tom Ogilvy

Stuart wrote in message
...
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?)



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.528 / Virus Database: 324 - Release Date: 16/10/2003

Missed it.

Regards and thanks.

"Tom Ogilvy" wrote in message
...
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


--
Regards,
Tom Ogilvy

Stuart wrote in message
...
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?)



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.528 / Virus Database: 324 - Release Date: 16/10/2003






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.528 / Virus Database: 324 - Release Date: 16/10/2003