error 1004 Range object

[Tony James]

Code that worked perfectly in Excel 97 is giving a run-time error
'1004': Application-defined or object-defined error in Excel 2003.

The error occurs on the following line in the code sample below.

Set rng = sceWB.Worksheets(ws.Name).Range(strNameAddress)


Function SearchOrderNum(OrderNum as String, DateStart as Date, DateEnd
as Date, resultsArray() As TimeUDT) As Boolean
Dim sceWB As Workbook
Dim ws As Worksheet
Dim strNameAddress as String
Dim rng As Range
Dim result As Range

Set sceWB = ThisWorkbook
For Each ws In sceWB.Worksheets
strNameAddress = ""
'set up multiple range for Find method, using named ranges with
offsets to Order No. column
For n = 1 To sceWB.Names.Count
If ws.Name = sceWB.Names(n).RefersToRange.Worksheet.Name Then
strNameAddress = strNameAddress &
sceWB.Names(n).RefersToRange.Offset(0, -4).Address & ","
End If
Next

strNameAddress = Left(strNameAddress, Len(strNameAddress) - 1)
'strip last comma

If strNameAddress <> "" Then
Set rng = sceWB.Worksheets(ws.Name).Range(strNameAddress)

With rng
Set result = .Find(OrderNum, LookIn:=xlValues)


The inner For loop sets up a string of columns to use in the Find
method.

For the first worksheet, strNameAddress is a string of 261 characters,
ie "$A:$A,$G:$G,$M:$M,$S:$S,$Y:$Y,$AE:$AE ......$GQ:$GQ".

I don't know if the error is caused by the length of the string, but
does anyone know how to solve this problem? Is there any reason why
it should work in Excel 97 but not 2003? Also I am open to
suggestions about any other ways to set up a Range object of multiple
columns to use in the Find method.

Thanks
Tony

 

[Dave Peterson]

Without setting up a workbook with a bunch of names, maybe an alternative to
creating a giant string of addresses is to just build a giant range.

dim myRng as range
if myrng is nothing then
set myrng = sceWB.Names(n).RefersToRange.Offset(0, -4)
else
set myrng = union(myrng, sceWB.Names(n).RefersToRange.Offset(0, -4))
end if

As long as those would be on the same worksheet.

Untested, too!

 

[Tony James]

Thanks for that Dave, I'll try your code sometime.

I managed to solve the problem by splitting the data over 2 worksheets,
so that the strNameAddress range string only reached about 120
characters.

 

[Tony James]

Excellent, just tested your code. It works well and gives the same
results as setting a string of columns, so would overcome the problem I
had. Thanks for your help.

For Each ws in sceWB.Worksheets
Set myRange = Nothing
For n = 1 To sceWB.Names.Count
If ws.Name = sceWB.Names(n).RefersToRange.Worksheet.Name Then
If myRange Is Nothing Then
Set myRange = sceWB.Names(n).RefersToRange.Offset(0, -4)
Else
Set myRange = Union(myRange,
sceWB.Names(n).RefersToRange.Offset(0, -4)
End If
End If
Next

If Not (myRange Is Nothing) Then
With myRange
Set result = .Find(OrderNum, LookIn:=xlValues)
'continue processing search result