I assemble data for each style from several spreadsheets, and combine them to create one "master" row for a style. So I have 2000 rows for 2000 styles. (Not 2000 sheets.) This "master" row is followed by a row for each size of that style. Each "master" contains a string that lists the sizes for that style. It is this string that I wanted to have sorted.
> I wonder if you need to sort the sizes at all ... aren't you adding > them in a particular order anyway?
Thanks for that suggestion! Originally the sizes for a style were in order 10, 11, 12, 13, 5, 6, 7, 8, 9. (I suspect the data were sorted with the sizes as strings.) For some reason it never occurred to me to sort the sheet that contains the sizes, but I just did, first by style second by size, and the data are now in the order I need. So I don't have to do a separate sort for each style. It was that, 2,000 sorts, that I think caused the trouble.
Once again I owe you all a great thanks, as well as my sanity.
Michael Trombetta
--- In ExcelVBA@yahoogroups.com, "David Smart" wrote:
>
> Look at the idea of inserting everything in the one place (unsorted) and
> then using one sort at the end to sort by style and size. (In fact, I'd
> wonder if you want to sort firstly by size, so that everything for a
> particular person's foot size is together.)
>
> > 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 don't know if I'm understanding this properly, but it sounds as though you
> will have 2,000 sheets (one per style). This could also be a problem.
>
> I wonder if you need to sort the sizes at all ... aren't you adding them in
> a particular order anyway?
>
> Regards, Dave S
>
> ----- Original Message -----
> From: "mickey11030"
> To:
> Sent: Monday, January 14, 2013 3:24 AM
> Subject: [ExcelVBA] Re: Puzzling Behavior
>
>
>
>
>
> 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]
> >
>
>
>
>
> ------------------------------------
>
> ----------------------------------
> 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
>
> ----------------------------------Yahoo! Groups Links
>
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (8) |
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