VBA code sort problems - recorded macro does not work with userform

[Forum Freak]

Hi

I need to sort a range of cells to put in numerical order. I recorded a
macro but when I transfered it to my userform button I hit problems

The following code sorts but I get a runtime error 13 type mismatch when I
exit the userform.
When I debug it has failed when it should be loading another userform

Sub Mysort1()

Sheets("Lists").Select
Range("CM38:CM42").Select
Selection.Sort Key1:=Range("CM38"), Order1:=xlAscending, Header:=xlGuess
_
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

I tried removing the selection part and used

Sheets("Lists").Range("CM38:CM42").Sort Key1:=Range("CM38"),
Order1:=xlAscending, Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

This time sorting does not take place and I get runtime 1004
The sort reference is not valid. Make sure that it's within the data you
want to sort, and the first SortBy box isn't the same or blank

I have searched the web but have not found a solution

Can anyone see what I am doing wrong.

Kenny
XP pro and office 2003

 

[Wigi]

Hello

Sheets("Lists").Range("CM38:CM42").Sort Key1:=Sheets("Lists").Range("CM38")

....

 

[Forum Freak]

Hi Wigi

Many many thanks for your response. A quick test shows it works. I cannot
thank you enough as I have wasted 2 days searching the net and got nothing
but a headache!

Regards
Kenny

 

[Dick Kusleika]

I tried removing the selection part and used

Sheets("Lists").Range("CM38:CM42").Sort Key1:=Range("CM38"),
Order1:=xlAscending, Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

This time sorting does not take place and I get runtime 1004
The sort reference is not valid. Make sure that it's within the data you
want to sort, and the first SortBy box isn't the same or blank

If Lists is not the activesheet, then Range("CM38") will point to whatever
sheet is active and it won't be in the sort range. Use this

With ThisWorkbook.Sheets("Lists")
.Range("CM38:CM42").Sort Key1:=.Range("CM38"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End With

Note the "." in front of the key1 range.

 

[Forum Freak]

Hi Dick

Thanks for the post - The fact that you explained why it did not work
helped. Indeed the sheet was not the activesheet.

Any chance you could look at my post of 18/7/08 (20:30) - replace microsoft
forms messagebox with something more meaningful -

Regards
Kenny