On Feb 10, 3:50*pm, Dave Peterson wrote:
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 wrote:

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

--

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 a

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