Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find function for a Range failing in excel 2003 and giving subscriptout of range error 9 problem but works fine in excel 2000
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find function for a Range failing in excel 2003 and givingsubscript out of range error 9 problem but works fine in excel 2000
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 On Feb 10, 10:53*am, 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find function for a Range failing in excel 2003 and givingsubscript out of range error 9 problem but works fine in excel 2000
On Feb 10, 11:43*am, wrote:
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 On Feb 10, 10:53*am, 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- Hide quoted text - - 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find function for a Range failing in excel 2003 and giving subscriptout of range error 9 problem but works fine in excel 2000
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find function for a Range failing in excel 2003 and givingsubscript out of range error 9 problem but works fine in excel 2000
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find function for a Range failing in excel 2003 and giving subscriptout of range error 9 problem but works fine in excel 2000
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: <<snipped 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2000 clocks -- Excel 2003 works fine. | Excel Programming | |||
xls file works fine in Excel 2000 and 2007 but crashes on opening in 2003 | Excel Discussion (Misc queries) | |||
Visual Basic Function works fine in Excell 2003 but not in Excel 2 | Excel Discussion (Misc queries) | |||
Pivot table is not valid in Excel 2000 but works fine in EXCEL 200 | Excel Discussion (Misc queries) | |||
#VALUE! error: vlookup works in Excel 2000 but not 2003 | Excel Discussion (Misc queries) |