Yes, qualifying worked.
Thanks also for the test advice.

Regards.

"J.E. McGimpsey" wrote in message
...
For one thing, you're not qualifying Columns(6) with the worksheet.
That shouldn't cause the error if your activesheet column 6 has text
- does it?

Try:

Set rng = .Columns(6).SpecialCells(xlConstants, XLTextValues)



Also, I've found it's nearly always worth testing the results of
SpecialCells"


Set rng = .Columns(6).SpecialCells(xlConstants, XLTextValues)
If rng is Nothing Then
<handle no text found
Else
<your existing code
End If



In article ,
"Stuart" wrote:

I'm trying to build an array where the start of each
element will be defined by a particular value in
Col 6.

In the following code I receive the above error message
on the line marked ***

For Each ws In Workbooks(SourceWorkbook).Worksheets()
With ws
Shtname = ws.Name
Dim i As Long
Dim varr()
Dim rng As Range, Cell As Range, rng1 As Range
ReDim varr(1 To 1)

If Not (UCase(.Name) = "MASTER" Or UCase(.Name) _
= "COVER" Or UCase(.Name) = "CONTENTS" Or _
UCase(.Name) = "SUMMARY") Then
*** Set rng = Columns(6).SpecialCells(xlConstants, xlTextValues)
For Each Cell In rng
If Cell.Value = "£" Then
If IsEmpty(varr(1)) Then
Set varr(1) = Cell
Else
ReDim Preserve varr(1 To UBound(varr) + 1)
Set varr(UBound(varr)) = Cell
End If
End If
Next
etc

As far as I can see with the test workbook, there are instances of
'£' in Col 6 in every worksheet. Why does the code appear to fail to
find '£', please?

Regards.


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




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