|
Range Find got Subscript out of range
below code works on Window 2000/Excel2000, but when upgrade to XP/Excel
2003 it failed with Subscript out of range: Please help. Thanks. Codes first read workbook sheet(2), get the data, then try to find summary sheet(1) with the same mfg header's column index, then back to sheet(2) keep processing For licnt = 1 To UBound(orsMfg_Name) oricolindex = 1 oriRowIndex = oriRowIndex + 1 objSheet(lisheetno).Range("A" & oriRowIndex) = orsMfg_Name(licnt).tName 'search to see which column on summary sheet for this mfg Set oriSearch = objSheet(1).Range("A10:IV11").Find(orsMfg_Name (licnt).tName, LookIn:=xlValue) --- Abend here on subscript out of range If Not oriSearch Is Nothing Then lisumcol = oriSearch.Column Set oriSearch = Nothing End If oricolindex = oricolindex + 7 Do While oricolindex <= oriTotalCols objSheet(lisheetno).Range(frfColName(oricolindex) & oriRowIndex).Formula = "=SUMIF($G$" _ & orirowstart & ":$G$" & orirowend & "," & """" & "=" & orsMfg_Name(licnt).tName _ & """" & "," & frfColName(oricolindex) & orirowstart _ & ":" & frfColName(oricolindex) & orirowend & ")" objSheet(lisheetno).Range(frfColName(oricolindex) & oriRowIndex).NumberFormat = "##,##0_);[Red](##,##0)" oricolindex = oricolindex + 1 Loop objSheet(1).Range(frfColName(lisumcol) & orisumrow + lisheetno).Formula = "=+" & Chr(39) & objSheet(lisheetno).Name & Chr(39) & "!" & frfColName(oriTotalCols) & oriRowIndex objSheet(1).Range(frfColName(lisumcol) & orisumrow + lisheetno).NumberFormat = "##,##0_);[Red](##,##0)" Next licnt |
Range Find got Subscript out of range
There is either no ObjSheet(1) or not prsMfg_Name with the index for licnt.
Unless the lowerbound of orsMfg_Name is higher than 1 (and since you have used that successfully in a previous line of code), then the likely suspect is no ObjSheet(1). -- Regards, Tom Ogilvy "PT91745" wrote in message ... below code works on Window 2000/Excel2000, but when upgrade to XP/Excel 2003 it failed with Subscript out of range: Please help. Thanks. Codes first read workbook sheet(2), get the data, then try to find summary sheet(1) with the same mfg header's column index, then back to sheet(2) keep processing For licnt = 1 To UBound(orsMfg_Name) oricolindex = 1 oriRowIndex = oriRowIndex + 1 objSheet(lisheetno).Range("A" & oriRowIndex) = orsMfg_Name(licnt).tName 'search to see which column on summary sheet for this mfg Set oriSearch = objSheet(1).Range("A10:IV11").Find(orsMfg_Name (licnt).tName, LookIn:=xlValue) --- Abend here on subscript out of range If Not oriSearch Is Nothing Then lisumcol = oriSearch.Column Set oriSearch = Nothing End If oricolindex = oricolindex + 7 Do While oricolindex <= oriTotalCols objSheet(lisheetno).Range(frfColName(oricolindex) & oriRowIndex).Formula = "=SUMIF($G$" _ & orirowstart & ":$G$" & orirowend & "," & """" & "=" & orsMfg_Name(licnt).tName _ & """" & "," & frfColName(oricolindex) & orirowstart _ & ":" & frfColName(oricolindex) & orirowend & ")" objSheet(lisheetno).Range(frfColName(oricolindex) & oriRowIndex).NumberFormat = "##,##0_);[Red](##,##0)" oricolindex = oricolindex + 1 Loop objSheet(1).Range(frfColName(lisumcol) & orisumrow + lisheetno).Formula = "=+" & Chr(39) & objSheet(lisheetno).Name & Chr(39) & "!" & frfColName(oriTotalCols) & oriRowIndex objSheet(1).Range(frfColName(lisumcol) & orisumrow + lisheetno).NumberFormat = "##,##0_);[Red](##,##0)" Next licnt |
Range Find got Subscript out of range
Hi Tom,
Thanks for your help. I add Redim for objsheet(1) as below, but still got the same error. I have the other report, only use single sheet and works fine after upgrade to XP/Excel2003, but for processing multi worksheets reports, they all failed, any idea? Thanks. ReDim Preserve objSheet(1) Set objSheet(1) = objExcel.Worksheets(1) objSheet(1).Activate objSheet(1).Select Set oriSearch = objSheet(1).Range("A10:IV11").Find(orsMfg_Name(lic nt).tName, LookIn:=xlValue) - Still abend here "Tom Ogilvy" wrote: There is either no ObjSheet(1) or not prsMfg_Name with the index for licnt. Unless the lowerbound of orsMfg_Name is higher than 1 (and since you have used that successfully in a previous line of code), then the likely suspect is no ObjSheet(1). -- Regards, Tom Ogilvy "PT91745" wrote in message ... below code works on Window 2000/Excel2000, but when upgrade to XP/Excel 2003 it failed with Subscript out of range: Please help. Thanks. Codes first read workbook sheet(2), get the data, then try to find summary sheet(1) with the same mfg header's column index, then back to sheet(2) keep processing For licnt = 1 To UBound(orsMfg_Name) oricolindex = 1 oriRowIndex = oriRowIndex + 1 objSheet(lisheetno).Range("A" & oriRowIndex) = orsMfg_Name(licnt).tName 'search to see which column on summary sheet for this mfg Set oriSearch = objSheet(1).Range("A10:IV11").Find(orsMfg_Name (licnt).tName, LookIn:=xlValue) --- Abend here on subscript out of range If Not oriSearch Is Nothing Then lisumcol = oriSearch.Column Set oriSearch = Nothing End If oricolindex = oricolindex + 7 Do While oricolindex <= oriTotalCols objSheet(lisheetno).Range(frfColName(oricolindex) & oriRowIndex).Formula = "=SUMIF($G$" _ & orirowstart & ":$G$" & orirowend & "," & """" & "=" & orsMfg_Name(licnt).tName _ & """" & "," & frfColName(oricolindex) & orirowstart _ & ":" & frfColName(oricolindex) & orirowend & ")" objSheet(lisheetno).Range(frfColName(oricolindex) & oriRowIndex).NumberFormat = "##,##0_);[Red](##,##0)" oricolindex = oricolindex + 1 Loop objSheet(1).Range(frfColName(lisumcol) & orisumrow + lisheetno).Formula = "=+" & Chr(39) & objSheet(lisheetno).Name & Chr(39) & "!" & frfColName(oriTotalCols) & oriRowIndex objSheet(1).Range(frfColName(lisumcol) & orisumrow + lisheetno).NumberFormat = "##,##0_);[Red](##,##0)" Next licnt |
Range Find got Subscript out of range
Looks like your down to stepping through your code and checking the values
of your variables at each step. -- Regards, Tom Ogilvy "PT91745" wrote in message ... Hi Tom, Thanks for your help. I add Redim for objsheet(1) as below, but still got the same error. I have the other report, only use single sheet and works fine after upgrade to XP/Excel2003, but for processing multi worksheets reports, they all failed, any idea? Thanks. ReDim Preserve objSheet(1) Set objSheet(1) = objExcel.Worksheets(1) objSheet(1).Activate objSheet(1).Select Set oriSearch = objSheet(1).Range("A10:IV11").Find(orsMfg_Name(lic nt).tName, LookIn:=xlValue) - Still abend here "Tom Ogilvy" wrote: There is either no ObjSheet(1) or not prsMfg_Name with the index for licnt. Unless the lowerbound of orsMfg_Name is higher than 1 (and since you have used that successfully in a previous line of code), then the likely suspect is no ObjSheet(1). -- Regards, Tom Ogilvy "PT91745" wrote in message ... below code works on Window 2000/Excel2000, but when upgrade to XP/Excel 2003 it failed with Subscript out of range: Please help. Thanks. Codes first read workbook sheet(2), get the data, then try to find summary sheet(1) with the same mfg header's column index, then back to sheet(2) keep processing For licnt = 1 To UBound(orsMfg_Name) oricolindex = 1 oriRowIndex = oriRowIndex + 1 objSheet(lisheetno).Range("A" & oriRowIndex) = orsMfg_Name(licnt).tName 'search to see which column on summary sheet for this mfg Set oriSearch = objSheet(1).Range("A10:IV11").Find(orsMfg_Name (licnt).tName, LookIn:=xlValue) --- Abend here on subscript out of range If Not oriSearch Is Nothing Then lisumcol = oriSearch.Column Set oriSearch = Nothing End If oricolindex = oricolindex + 7 Do While oricolindex <= oriTotalCols objSheet(lisheetno).Range(frfColName(oricolindex) & oriRowIndex).Formula = "=SUMIF($G$" _ & orirowstart & ":$G$" & orirowend & "," & """" & "=" & orsMfg_Name(licnt).tName _ & """" & "," & frfColName(oricolindex) & orirowstart _ & ":" & frfColName(oricolindex) & orirowend & ")" objSheet(lisheetno).Range(frfColName(oricolindex) & oriRowIndex).NumberFormat = "##,##0_);[Red](##,##0)" oricolindex = oricolindex + 1 Loop objSheet(1).Range(frfColName(lisumcol) & orisumrow + lisheetno).Formula = "=+" & Chr(39) & objSheet(lisheetno).Name & Chr(39) & "!" & frfColName(oriTotalCols) & oriRowIndex objSheet(1).Range(frfColName(lisumcol) & orisumrow + lisheetno).NumberFormat = "##,##0_);[Red](##,##0)" Next licnt |
Range Find got Subscript out of range
I finally figure out what is wrong, on excel 2000 Find function works on
both LookIn:=xlValue or LookIn:=xlValues. But Excel 2003 will abend with subscript out of range when use LookIn:=xlValue so the code shall change to : Set oriSearch = objSheet(orisheet).Range("A2:IV100").FindFind(orsM fg_Name (licnt).tName, LookIn:=xlValues) "Tom Ogilvy" wrote: Looks like your down to stepping through your code and checking the values of your variables at each step. -- Regards, Tom Ogilvy "PT91745" wrote in message ... Hi Tom, Thanks for your help. I add Redim for objsheet(1) as below, but still got the same error. I have the other report, only use single sheet and works fine after upgrade to XP/Excel2003, but for processing multi worksheets reports, they all failed, any idea? Thanks. ReDim Preserve objSheet(1) Set objSheet(1) = objExcel.Worksheets(1) objSheet(1).Activate objSheet(1).Select Set oriSearch = objSheet(1).Range("A10:IV11").Find(orsMfg_Name(lic nt).tName, LookIn:=xlValue) - Still abend here "Tom Ogilvy" wrote: There is either no ObjSheet(1) or not prsMfg_Name with the index for licnt. Unless the lowerbound of orsMfg_Name is higher than 1 (and since you have used that successfully in a previous line of code), then the likely suspect is no ObjSheet(1). -- Regards, Tom Ogilvy "PT91745" wrote in message ... below code works on Window 2000/Excel2000, but when upgrade to XP/Excel 2003 it failed with Subscript out of range: Please help. Thanks. Codes first read workbook sheet(2), get the data, then try to find summary sheet(1) with the same mfg header's column index, then back to sheet(2) keep processing For licnt = 1 To UBound(orsMfg_Name) oricolindex = 1 oriRowIndex = oriRowIndex + 1 objSheet(lisheetno).Range("A" & oriRowIndex) = orsMfg_Name(licnt).tName 'search to see which column on summary sheet for this mfg Set oriSearch = objSheet(1).Range("A10:IV11").Find(orsMfg_Name (licnt).tName, LookIn:=xlValue) --- Abend here on subscript out of range If Not oriSearch Is Nothing Then lisumcol = oriSearch.Column Set oriSearch = Nothing End If oricolindex = oricolindex + 7 Do While oricolindex <= oriTotalCols objSheet(lisheetno).Range(frfColName(oricolindex) & oriRowIndex).Formula = "=SUMIF($G$" _ & orirowstart & ":$G$" & orirowend & "," & """" & "=" & orsMfg_Name(licnt).tName _ & """" & "," & frfColName(oricolindex) & orirowstart _ & ":" & frfColName(oricolindex) & orirowend & ")" objSheet(lisheetno).Range(frfColName(oricolindex) & oriRowIndex).NumberFormat = "##,##0_);[Red](##,##0)" oricolindex = oricolindex + 1 Loop objSheet(1).Range(frfColName(lisumcol) & orisumrow + lisheetno).Formula = "=+" & Chr(39) & objSheet(lisheetno).Name & Chr(39) & "!" & frfColName(oriTotalCols) & oriRowIndex objSheet(1).Range(frfColName(lisumcol) & orisumrow + lisheetno).NumberFormat = "##,##0_);[Red](##,##0)" Next licnt |
Range Find got Subscript out of range
From your other thread:
xlvalue = xlValues? But xl2003 won't crash when you're looking through values. I'd still bet that your worksheet didn't exist. orisheet may not be what you think it is. PT91745 wrote: I finally figure out what is wrong, on excel 2000 Find function works on both LookIn:=xlValue or LookIn:=xlValues. But Excel 2003 will abend with subscript out of range when use LookIn:=xlValue so the code shall change to : Set oriSearch = objSheet(orisheet).Range("A2:IV100").FindFind(orsM fg_Name (licnt).tName, LookIn:=xlValues) "Tom Ogilvy" wrote: Looks like your down to stepping through your code and checking the values of your variables at each step. -- Regards, Tom Ogilvy "PT91745" wrote in message ... Hi Tom, Thanks for your help. I add Redim for objsheet(1) as below, but still got the same error. I have the other report, only use single sheet and works fine after upgrade to XP/Excel2003, but for processing multi worksheets reports, they all failed, any idea? Thanks. ReDim Preserve objSheet(1) Set objSheet(1) = objExcel.Worksheets(1) objSheet(1).Activate objSheet(1).Select Set oriSearch = objSheet(1).Range("A10:IV11").Find(orsMfg_Name(lic nt).tName, LookIn:=xlValue) - Still abend here "Tom Ogilvy" wrote: There is either no ObjSheet(1) or not prsMfg_Name with the index for licnt. Unless the lowerbound of orsMfg_Name is higher than 1 (and since you have used that successfully in a previous line of code), then the likely suspect is no ObjSheet(1). -- Regards, Tom Ogilvy "PT91745" wrote in message ... below code works on Window 2000/Excel2000, but when upgrade to XP/Excel 2003 it failed with Subscript out of range: Please help. Thanks. Codes first read workbook sheet(2), get the data, then try to find summary sheet(1) with the same mfg header's column index, then back to sheet(2) keep processing For licnt = 1 To UBound(orsMfg_Name) oricolindex = 1 oriRowIndex = oriRowIndex + 1 objSheet(lisheetno).Range("A" & oriRowIndex) = orsMfg_Name(licnt).tName 'search to see which column on summary sheet for this mfg Set oriSearch = objSheet(1).Range("A10:IV11").Find(orsMfg_Name (licnt).tName, LookIn:=xlValue) --- Abend here on subscript out of range If Not oriSearch Is Nothing Then lisumcol = oriSearch.Column Set oriSearch = Nothing End If oricolindex = oricolindex + 7 Do While oricolindex <= oriTotalCols objSheet(lisheetno).Range(frfColName(oricolindex) & oriRowIndex).Formula = "=SUMIF($G$" _ & orirowstart & ":$G$" & orirowend & "," & """" & "=" & orsMfg_Name(licnt).tName _ & """" & "," & frfColName(oricolindex) & orirowstart _ & ":" & frfColName(oricolindex) & orirowend & ")" objSheet(lisheetno).Range(frfColName(oricolindex) & oriRowIndex).NumberFormat = "##,##0_);[Red](##,##0)" oricolindex = oricolindex + 1 Loop objSheet(1).Range(frfColName(lisumcol) & orisumrow + lisheetno).Formula = "=+" & Chr(39) & objSheet(lisheetno).Name & Chr(39) & "!" & frfColName(oriTotalCols) & oriRowIndex objSheet(1).Range(frfColName(lisumcol) & orisumrow + lisheetno).NumberFormat = "##,##0_);[Red](##,##0)" Next licnt -- Dave Peterson |
| All times are GMT +1. The time now is 12:25 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com