Thank you all for your suggestions.
I am adding about 2,000 shoes styles to my web store. Each shoe style has about 10 sizes, each of which must have its own row for inventory purposes. Different styles have different size ranges, 5 to 12, 6 to 14, S, M, L, etc. As I add each size for a particular style, I store the size in a separate sheet. When I finish with a style, I sort the sizes, and then concatenate them into a string, which I enter as part of the description of that style.
I thought about storing the sizes in an array, and sorting them myself, but I figured Excel would have no trouble sorting less than 12 cells. Perhaps the sort itself imposes an overhead, which when repeated more than 1,000 times, causes the error.
I will contact the people who host my store and ask if it is necessary that the sizes be sorted. If not, the problem is solved, but if they must be sorted, I will sort an array myself.
To answer Lisa, there is no recursion involved in the code.
Thanks again for your always helpful suggestions.
Michael Trombetta
--- In ExcelVBA@yahoogroups.com, Derek Turner wrote:
>
> Dear Michael
>
> Another explanation is that this is an automation error.
>
> You could try putting in a short pause before you call the routine to allow Excel to do some housekeeping. Or instead a longer pause if you trap for error 50290 within the routine.
>
> A workaround might be to copy the range into a variant array and then sort that.
>
> Also you don't need the Activate (or the Select), put With ActiveWorkbook.Worksheets("Sizes") at the start of the routine and use the dot notation for everything else.
>
> We are all curious to know why you are calling this so many times. Please explain.
>
> Regards
>
> Derek Turner +++
>
>
>
>
>
> >________________________________
> > From: "1z@..." <1z@...>
> >To: ExcelVBA@yahoogroups.com
> >Sent: Sunday, 13 January 2013, 12:41
> >Subject: Re: [ExcelVBA] Puzzling Behavior
> >
> >
> >Â
> >Michael,
> >
> >What's the code that calls the subroutine?
> >Is there any sort of recursion involved?
> >
> >Lisa
> >
> >Sent: Sun, Jan 13, 2013 4:48 am
> >
> >Subject: [ExcelVBA] Puzzling Behavior
> >
> >I am running a program that pulls selected fields from several worksheets (all
> >in one workbook) to produce new worksheet with the combined data. At one point I
> >sort a sheet with a few (< 15) rows. The program runs and performs the sort many
> >times, but then halts in the sort routine. I created the sort routine by
> >recording a macro.
> >
> >Sub SortSizes(SizeCounter)
> >Sheets("Sizes").Activate
> >Sheets("Sizes").Range("A1").Select
> >ActiveWorkbook.Worksheets("Sizes").Sort.SortFields.Clear
> >ActiveWorkbook.Worksheets("Sizes").Sort.SortFields.Add Key:=Range("A1:A" &
> >SizeCounter), _
> >SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
> >xlSortTextAsNumbers
> >With ActiveWorkbook.Worksheets("Sizes").Sort
> >.SetRange Range("A1:A10")
> >.Header = xlNo
> >.MatchCase = False
> >.Orientation = xlTopToBottom
> >.SortMethod = xlPinYin
> >.Apply
> >End With
> >End Sub
> >
> >The first time it halted after about 1,400 executions on the statement
> >Sheets("Sizes").Activate. The second time it halted after about 1,700 executions
> >on the .Apply statement. In both cases the error message was: Run-time error
> >'50290'/Application-defined or object-defined error. The Sizes sheet is there,
> >with about 10 sizes to be sorted.
> >
> >Note: I just noticed I forgot to change the .SetRange Range("A1:A10) to
> >Range("A1:A" & SizeCounter), but I can't see how that would cause an error on
> >the Activate or Apply statements
> >
> >Can anyone offer any explanation or even some experiments I could run to get to
> >the bottom of this mystery?
> >
> >I'm running Excel 2007 on Windows 7 system.
> >
> >Thanks for any help
> >Michael Trombetta
> >
> >[Non-text portions of this message have been removed]
> >
> >
> >
> >
> >
>
> [Non-text portions of this message have been removed]
>
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (5) |
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar