Selection.sort Run-time error '1004'

[peter.tilm]

Hello,

I've got a problem when trying to sort a selection (Range of a
worksheet). Here is the code snipped (similar to the one recorded with
record a macro):

With QACPackageWS
Set rng = .Range(.Cells(1, 1), .Cells(row, col))
rng.Select
Selection.Sort Key1:=Range("E2"), Order1:=xlDescending,
Key2:=Range("G2") _
, Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1,
MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal
End With

Excel/VBA stops program execution with a Run-time error '1004'.
Anyone a clue what i did wrong? Thanks for Support!

BR/
tilmp

 

[paul.robinson]

You must activate the sheet before using select could be the problem.
Better though to skip the select altogether. Also, are the E2 and G2
cells on the same sheet, in which case you need .Range("E2") and
..Range("G2")

With QACPackageWS
Set rng = .Range(.Cells(1, 1), .Cells(row, col))
rng.Sort Key1:=Range("E2"), Order1:=xlDescending,
Key2:=Range("G2") _
, Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1,
MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal
End With

I assume you have used the line wrapping characters (space followed by
underscore) that lets you break code lines?

regards
Paul

 

[Peter T]

Are you by chance trying to use this in XL2000 (although originally recorded
in a later version). If so the arguments DataOption1 & DataOption2 are n/a.

Otherwise verify the sort keys are within the range.

Regards,
Peter T