[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

__._,_.___

Reply via web post

Reply to sender

Reply to group

Start a New Topic

Messages in this topic (1)

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

.

__,_._,___