Find function for a Range failing in excel 2003 and giving subscriptout of range error 9 problem but

[Prince]

Set mwbStore = Application.Workbooks.Add()
For n = 0 To UBound(mwbD)
For Each wks In mwbD(n).Worksheets
Set rng = wks.UsedRange.Columns(1)
Set cel = Nothing
*******Problem Line
Set cel = rng.Find(What:=storeNum, LookAt:=xlWhole, _
LookIn:=xlValue,
SearchOrder:=xlByColumns)
*****End Problem Line

The basic thing is that in a workbook BUDGET1 sheet we are having
first column as store names defind as a range.

It is picking the store name and trying to find that store name in
that first column defined as range in BUDGET1 sheet.

The same function

Set cel = rng.Find(What:=storeNum, LookAt:=xlWhole, _
LookIn:=xlValue,
SearchOrder:=xlByColumns)

is running fine in excel 2000 but gives Subscript out of range error 9
in excel 2003

 

[paul.robinson]

Hi
Possibly the first thing you checked, but Is storeNum actually in rng
in your 2003 workbook? Try to find it manually using the excel toolbar
Find...It may have a leading/trailing space in rng that you can't see?
regards
Paul

 

[Prince]

Hi
Possibly the first thing you checked, but Is storeNum actually in rng
in your 2003 workbook? Try to find it manually using the excel toolbar
Find...It may have a leading/trailing space in rng that you can't see?
regards
Paul








- Show quoted text -

Hello Paul,

I have made the check by typjng Debug.Print rng.Address
In both the case in 2000 and 2003 it shows A$1A$199 which is the first
column of the worksheet wh.xls containing all the store number.

In 2000 that set function returns 2 but in 2003 it goes to Terminate
function showing the error subscript out of range.

Hence it is not a range defined but it is just the range assigned
during run time hence both the version shows the same address of the
range.

Please guide.

Regards,

Prince

 

[Dave Peterson]

I'm surprised that it worked in xl2k. You have a typo.

It's xlvalues (with an S), not xlvalue.

and I always include all the parms to the .find command. Otherwise, you'll be
inheriting those settings from the last Find (either from code or by the user
interface).

Option Explicit
Sub testme()

Dim n As Long
Dim mwbD As Variant
Dim wks As Worksheet
Dim storeNum As String
Dim cel As Range
Dim rng As Range

storeNum = "$a$3"

mwbD = Array(Workbooks("book1.xls"), Workbooks("book2.xls"))

For n = LBound(mwbD) To UBound(mwbD)
For Each wks In mwbD(n).Worksheets
Set rng = wks.UsedRange.Columns(1)
With rng
Set cel = .Cells.Find(What:=storeNum, _
LookAt:=xlWhole, _
LookIn:=xlValues, _
SearchOrder:=xlByColumns, _
searchdirection:=xlNext, _
MatchCase:=False, _
after:=.Cells(.Cells.Count))
End With
Next wks
Next n
End Sub

You don't need that "set cel = nothing", either. The .find will either find it
(not-nothing) or fail to find it (nothing).

 

[Prince]

I'm surprised that it worked in xl2k.  You have a typo.

It's xlvalues (with an S), not xlvalue.

and I always include all the parms to the .find command.  Otherwise, you'll be
inheriting those settings from the last Find (either from code or by the user
interface).

Option Explicit
Sub testme()

    Dim n As Long
    Dim mwbD As Variant
    Dim wks As Worksheet
    Dim storeNum As String
    Dim cel As Range
    Dim rng As Range

    storeNum = "$a$3"

    mwbD = Array(Workbooks("book1.xls"), Workbooks("book2.xls"))

    For n = LBound(mwbD) To UBound(mwbD)
        For Each wks In mwbD(n).Worksheets
            Set rng = wks.UsedRange.Columns(1)
            With rng
                Set cel = .Cells.Find(What:=storeNum,_
                            LookAt:=xlWhole, _
                            LookIn:=xlValues, _
                            SearchOrder:=xlByColumns, _
                            searchdirection:=xlNext, _
                            MatchCase:=False, _
                            after:=.Cells(.Cells.Count))
            End With
        Next wks
    Next n
End Sub

You don't need that "set cel = nothing", either.  The .find will either find it
(not-nothing) or fail to find it (nothing).











--

Dave Peterson- Hide quoted text -

- Show quoted text -

Yes Dave,

I found that and it is working but yes i was just wondering how come
xlvalue work in xl 2000 but it fails in xl2003. I had made the
following change and it worked.

Please tell me if you know the reason for that bcoz i have debugged
the code in xl2000 and there it is not giving any error.

Thanx a lot for your cooperation and guidance.

I was doing the migration of the application to xl2003 hence i am
haviong lot of queries. Some of them are:

The fetching of the data from the excel sheets by variouis formulaes
into the corresponding screen is taking just the double time in the
application 2003.

Hence I was wondering and trying to find out the reason.

Can you please tell that has such issues been with excel2003 that the
formulaes and fetching of data if migrated to excel2003 from excel2000
makes the apllication and fetching of data slower.

Regards,

Prince

 

[Dave Peterson]

I don't have xl2k installed, so I can't test to see if causes trouble.

But in xl2003, there's a difference in those constants:
?xlvalues
-4163
?xlvalue
2
(from the immediate window in the VBE)

Maybe you can check if the constants are the same in xl2k.

Or maybe xl2k is just more forgiving????

I've never noticed a difference in speed between xl2k and xl2003.

But maybe I've never done the "fetching" you have.

If you describe what you "fetching" means, maybe someone who has done it like
you have will have an idea.

Prince wrote: