Range Find got Subscript out of range

P

PT91745

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
 
T

Tom Ogilvy

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).
 
P

PT91745

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(licnt).tName, LookIn:=xlValue)
-> Still abend here


Tom Ogilvy said:
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 said:
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
Click to expand...
Click to expand...
 
T

Tom Ogilvy

Looks like your down to stepping through your code and checking the values
of your variables at each step.

--
Regards,
Tom Ogilvy

PT91745 said:
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(licnt).tName, LookIn:=xlValue)
-> Still abend here


Tom Ogilvy said:
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 said:
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
Click to expand...
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 & "," & """" &
Click to expand...
"="
&
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 &
Click to expand...
Chr(39)
&
"!" & frfColName(oriTotalCols) & oriRowIndex
objSheet(1).Range(frfColName(lisumcol) & orisumrow +
lisheetno).NumberFormat = "##,##0_);[Red](##,##0)"

Next licnt
Click to expand...
Click to expand...
Click to expand...
 
P

PT91745

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(orsMfg_Name
(licnt).tName, LookIn:=xlValues)

Tom Ogilvy said:
Looks like your down to stepping through your code and checking the values
of your variables at each step.

--
Regards,
Tom Ogilvy

PT91745 said:
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(licnt).tName, LookIn:=xlValue)
-> Still abend here


Tom Ogilvy said:
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

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
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

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.
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(orsMfg_Name
(licnt).tName, LookIn:=xlValues)

Tom Ogilvy said:
Looks like your down to stepping through your code and checking the values
of your variables at each step.

--
Regards,
Tom Ogilvy

PT91745 said:
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(licnt).tName, LookIn:=xlValue)
-> Still abend here


:

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

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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

"Subscript out of range" error when connecting with excel 0
Excel Macro Problem, Add-in need to work in every workbook & Error:9 Subscript out of range 0

Top