|
runtime error 9 -- subscript out of range
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 |
runtime error 9 -- subscript out of range
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. Milind Keer wrote: 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 |
runtime error 9 -- subscript out of range
dave, could it be a memory issue?
-- Gary "Dave Peterson" wrote in message ... 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. Milind Keer wrote: 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 |
runtime error 9 -- subscript out of range
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! "Milind Keer" wrote: 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 |
runtime error 9 -- subscript out of range
That wouldn't be my first guess.
Gary Keramidas wrote: dave, could it be a memory issue? -- Gary "Dave Peterson" wrote in message ... 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. Milind Keer wrote: 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 -- Dave Peterson |
runtime error 9 -- subscript out of range
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" wrote in message ... 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 |
runtime error 9 -- subscript out of range
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 |
runtime error 9 -- subscript out of range
Just a curiosity question...
Why not copy the visible range after the filter? Then paste that visible range where you want it. Milind Keer wrote: 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 |
runtime error 9 -- subscript out of range
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" wrote: Just a curiosity question... Why not copy the visible range after the filter? Then paste that visible range where you want it. Milind Keer wrote: 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 |
runtime error 9 -- subscript out of range
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 wrote: 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" wrote: Just a curiosity question... Why not copy the visible range after the filter? Then paste that visible range where you want it. Milind Keer wrote: 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 -- Dave Peterson |
runtime error 9 -- subscript out of range
Hi Dave
Thanks for the prompt reply. I will certainly try this and let you know the outcome... thanks a ton!! Milind "Dave Peterson" wrote: 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 wrote: 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" wrote: Just a curiosity question... Why not copy the visible range after the filter? Then paste that visible range where you want it. Milind Keer wrote: 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 -- Dave Peterson |
runtime error 9 -- subscript out of range
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 |
runtime error 9 -- subscript out of range
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. Milind Keer wrote: 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 |
| All times are GMT +1. The time now is 03:44 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com