runtime error 9 -- subscript out of range

[Milind Keer]

Hi

I am getting runtime error 9 - subscript out of range for following code...

This code is basically pasting values to 'UK - SI - CHT' sheet from an array.

the ubound value of the array is 55000, it is successfully pasting 2425
records but then giving 'runtime error 9 -- subscript out of range'

Wat could be the reason??? Please help...


vUKOnlyXLReport = "SI CtS May-08 UK Only - detail plus graphs CCI%.xls"
Workbooks.Open (vGlobalReportPath & vUKOnlyXLReport)
Workbooks(vUKOnlyXLReport).Worksheets("UK - SI - CHT").Activate
vMatch = "Systems Integration & TechnologySystems IntegrationUK,
IrelandUnited KingdomCommunications & High Tech"

Dim iR As Long
Dim iRow As Long

iR = 2
For iRow = 2 To UBound(vaGrowthPlatform)
If vMatch = vaGrowthPlatform(iRow) & vaServiceGroup(iRow) &
vaGeography(iRow) & vaGeoCity(iRow) & vaOG(iRow) Then
Workbooks(vUKOnlyXLReport).Worksheets("UK - SI - CHT").Range("A"
& iR).Value = vaWorkforce(iRow)
Workbooks(vUKOnlyXLReport).Worksheets("UK - SI - CHT").Range("B"
& iR).Value = vaOG(iRow)
Workbooks(vUKOnlyXLReport).Worksheets("UK - SI - CHT").Range("C"
& iR).Value = vaGeography(iRow)
Workbooks(vUKOnlyXLReport).Worksheets("UK - SI - CHT").Range("D"
& iR).Value = vaGeoCity(iRow)
Workbooks(vUKOnlyXLReport).Worksheets("UK - SI - CHT").Range("F"
& iR).Value = vaSumOfhours_total_ytd(iRow)
Workbooks(vUKOnlyXLReport).Worksheets("UK - SI - CHT").Range("G"
& iR).Value = vagdn_locale_cd(iRow)
Workbooks(vUKOnlyXLReport).Worksheets("UK - SI - CHT").Range("H"
& iR).Value = vamstr_client_name(iRow)
Workbooks(vUKOnlyXLReport).Worksheets("UK - SI - CHT").Range("I"
& iR).Value = vamstr_contract_nbr(iRow)
Workbooks(vUKOnlyXLReport).Worksheets("UK - SI - CHT").Range("J"
& iR).Value = vamstr_contract_name(iRow)
Workbooks(vUKOnlyXLReport).Worksheets("UK - SI - CHT").Range("K"
& iR).Value = vawbselement_cd(iRow)
Workbooks(vUKOnlyXLReport).Worksheets("UK - SI - CHT").Range("L"
& iR).Value = vawbselement_desc(iRow)

Workbooks(vUKOnlyXLReport).Worksheets("UK - SI - CHT").Range("T"
& iR).Value = vaSumOfcosts_payroll_ytd(iRow)
Workbooks(vUKOnlyXLReport).Worksheets("UK - SI - CHT").Range("U"
& iR).Value = vaSumOfcosts_loads_ytd(iRow)
Workbooks(vUKOnlyXLReport).Worksheets("UK - SI - CHT").Range("V"
& iR).Value = vaSumOfcosts_seat_charges_ytd(iRow)

SumofYTDLoadedcosts = (vaSumOfcosts_payroll_ytd(iRow) +
vaSumOfcosts_loads_ytd(iRow) + vaSumOfcosts_loads_ytd(iRow))

If vaSumOfhours_total_ytd(iRow) <> 0 Then
CtS = SumofYTDLoadedcosts / vaSumOfhours_total_ytd(iRow)
Else
CtS = ""
End If

Workbooks(vUKOnlyXLReport).Worksheets("UK - SI - CHT").Range("E"
& iR).Value = SumofYTDLoadedcosts
Workbooks(vUKOnlyXLReport).Worksheets("UK - SI - CHT").Range("M"
& iR).Value = CtS

iR = iR + 1
End If
Next iRow

Workbooks(vUKOnlyXLReport).Close SaveChanges:=True


Thanks!!
Milind

 

[Dave Peterson]

That error means that whatever you referred in your collections (worksheets() or
workbooks()) or your arrays (vaworkforce() or vaog() or ...) doesn't exist.

But I couldn't even guess what line caused the error.

But if it's working 2425 times, I'm guessing that it's one of your arrays.

 

[Gary Keramidas]

dave, could it be a memory issue?

--


Gary

That error means that whatever you referred in your collections (worksheets()
or
workbooks()) or your arrays (vaworkforce() or vaog() or ...) doesn't exist.

But I couldn't even guess what line caused the error.

But if it's working 2425 times, I'm guessing that it's one of your arrays.

 

[Chris Van Nuys]

Hi Milind

I believe you are running into this due to memory restrictions.

One thing you should test, try creating a seperate module that just creates
an array, and try filling the array with 2424 values ... then try filling it
with 2,425 values.

Does it break at 2425?

One way you might overcome this restriction might be to free or deallocate
the memory associated with the array by using the Erase command.

After writing the 2424th value, Erase the array using:
Erase array

Then either start rebuilding that same array from 0 to 2424 values again
with the next set of data, or start filling up a new array.

Deallocating the memory associated with the array should fix the problem if
it is indeed a memory issue.

Let me know if this helps! (or if it doesn't)
--
Best of Luck!,

Chris Van Nuys

Become more effective and efficient: Drive your business career by
mastering Excel!

 

[Dave Peterson]

That wouldn't be my first guess.

dave, could it be a memory issue?

 

[Tim Williams]

Seems like you have multiple arrays: are you sure they're *all* the same
size?

Also, you might consider simplifying your code somewhat:

If.... Then

With Workbooks(vUKOnlyXLReport).Worksheets("UK - SI - CHT").Rows(iRow)
.Cells(1).value = vaWorkforce(iRow)
.Cells(2).Value = vaOG(iRow)
'etc etc
End With

'rest of code

End If

Should also run a bit faster.

Tim

 

[Milind Keer]

I just got up from yesterdays late night work n saw all ur responses, thanks
a lot for that.

few more things which may help you to troubleshoot this issue,

1. My source file has 55000 rows and 20 columns.
2. Here I want to a put filter and want to paste filtered data in seperate XL.
3. Filter criteria would always return maximum 5000 rows which I want to
paste in seperate XL.

What I am doing
1. Using Do While Loop I am pulling out all 55000 row value in an array (1
single dimension array for each column, hnece 20 arrays)
2. Note: here when I used single Do while loop XL was causing a problem
hence i have written 20 different Do While loops and now its working... i
dont know how but I am trying to figure it out.
3. Once I have data in my arrays I am comparing each row against my filter
criteria and if it matches inserting in new XL (above) code.
4. Now till 2425 code is inserting values and giving me 'runtime error 9 --
subscript out of range' for 2426 th record.

Now is there any other way to handle above scenario?? or should I proceed
with my logic and try to troubleshoot the issue?

Thanks again!! and I hope I am not bothering you on weekend... Have a
nice weekend ahead...

Thanks a lot!!
Milind Keer

 

[Dave Peterson]

Just a curiosity question...

Why not copy the visible range after the filter? Then paste that visible range
where you want it.

 

[Milind Keer]

Yes!! I Can do that but in source file there are 100 columns and I am
intrested only in 20 columns. I know I can still do copy paste but somehow
not really convince in doing that.

-- milind

 

[Dave Peterson]

If your 20 columns are contiguous, it would be a single copy|paste.

If your 20 columns are non-contiguous, it would be at most 20 copy|pastes.

I gotta believe 20 copy|pastes would be much quicker than picking up 20*5000
values and then looping through the arrays to assign those 20*5000 values to
cells.

I'd do some minor experiments if I were you.

This copies the entire visible range (without the header) to another sheet:

Option Explicit
Sub testme1()

Dim RngToCopy As Range
Dim wks As Worksheet
Dim DestCell As Range

Set wks = Worksheets("Sheet1")

'what worksheet and where to start
Set DestCell = Worksheets("sheet2").Range("a17")

With wks
'apply your filtering here!
With .AutoFilter.Range
If .Columns(1).Cells _
.SpecialCells(xlCellTypeVisible).Cells.Count = 1 Then
'only headers are visible, so skip it
Else
'"remove" a row and come down 1 row
Set RngToCopy = .Resize(.Rows.Count - 1, .Columns.Count) _
.Offset(1, 0)
RngToCopy.Copy _
Destination:=DestCell
End If
End With
End With
End Sub

If the columns were discontiguous:

Option Explicit
Sub testme2()

Dim RngToCopy As Range
Dim wks As Worksheet
Dim DestCell As Range
Dim myCols As Variant
Dim cCtr As Long

Set wks = Worksheets("Sheet1")

'what worksheet and where to start
Set DestCell = Worksheets("sheet2").Range("a17")

myCols = Array(3, 8, 12, 15)

With wks
'apply your filtering here!
With .AutoFilter.Range
If .Columns(1).Cells _
.SpecialCells(xlCellTypeVisible).Cells.Count = 1 Then
'only headers are visible, so skip it
Else
'"remove" a row and come down 1 row
For cCtr = LBound(myCols) To UBound(myCols)
Set RngToCopy = .Resize(.Rows.Count - 1, 1) _
.Offset(1, myCols(cCtr) - 1)
RngToCopy.Copy _
Destination:=DestCell
Set DestCell = DestCell.Offset(0, 1)
Next cCtr
End If
End With
End With
End Sub

 

[Milind Keer]

Hi Dave

Thanks for the prompt reply. I will certainly try this and let you know the
outcome...

thanks a ton!!
Milind

 

[Milind Keer]

Hi

Just gone through your code, and quick question, do I need to put filter
manually??

I have got several conditions and for each there is a different filter
criteria? and dont want to do that manually...

can we write a code to put filter?? if yes can you please share how??

Cheers!!
Milind

 

[Dave Peterson]

No. I manually filtered the data just for my testing. (I didn't have any idea
how/what you're filtering, so I just did it through the userinterface.)

You can filter the range in code. Just record a macro when you do it manually.

Then you can add it to the complete code.