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

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

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

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

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

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