Re: [ExcelVBA] Puzzling Behavior

 

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@compuserve.com" 1z@compuserve.com>
>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 (4)

Recent Activity:

• New Members 1

Visit Your Group

----------------------------------
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

----------------------------------

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use • Send us Feedback

.

__,_._,___