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

Stuart

why do you believe the routine is not finding "£" signs ? What do you next
that doesn't work as you expect it to ?

The following extract from your code finds "£" signs in column 6 ... but
each entry in the array just contains a "£" sign. Is that what you expect ?

Dim i As Long
Dim varr()
Dim rng As Range, Cell As Range, rng1 As Range
ReDim varr(1 To 1)
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

In my test I have four cells with values, only three of them "£" signs. I
ended up with an array of three elements, each containing a "£" sign.
Doesn't seem that useful an exercise but maybe I've just taken it out of
context ? If the cell contains anything besides the "£" sign it will be
ignored, for example, "£x"

Maybe you need: If Cell.Value Like "£*" Then

Another thought: if the contents of the cell is interpreted as a monetary
value then I don't think SpecialCells(xlConstants, xlTextValues) picks it
up.

As you are running this across all the worksheets, how do you plan to use
the information gathered ?

I think you need to step through your code to see what cells are included in
rng and how they are handled.

Regards

Trevor


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

Thanks for the reply.

I've taken the problem a little further, at least as far as
my limited knowledge allows.

My latest post explains what I've been using the array
for.......ie to collect the 'page' start instances in col(6).

Regards and thanks.

"Trevor Shuttleworth" wrote in message
...
Stuart

why do you believe the routine is not finding "£" signs ? What do you
next
that doesn't work as you expect it to ?

The following extract from your code finds "£" signs in column 6 ... but
each entry in the array just contains a "£" sign. Is that what you expect
?

Dim i As Long
Dim varr()
Dim rng As Range, Cell As Range, rng1 As Range
ReDim varr(1 To 1)
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

In my test I have four cells with values, only three of them "£" signs. I
ended up with an array of three elements, each containing a "£" sign.
Doesn't seem that useful an exercise but maybe I've just taken it out of
context ? If the cell contains anything besides the "£" sign it will be
ignored, for example, "£x"

Maybe you need: If Cell.Value Like "£*" Then

Another thought: if the contents of the cell is interpreted as a monetary
value then I don't think SpecialCells(xlConstants, xlTextValues) picks it
up.

As you are running this across all the worksheets, how do you plan to use
the information gathered ?

I think you need to step through your code to see what cells are included
in
rng and how they are handled.

Regards

Trevor


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