Bombing on final sort command . . .

[Rich]

A user sent me a spreadsheet with a recorded macro. He needed it changed to
work on different worksheets within the workbook.

I changed the macro so that it used "ActiveSheet." instead of a particular
worksheet.

I also changed it so that it finds the area to sort and then assigns a Var
name to the Range.

But, now it bombs on a particular line, which I have rem'd as, "THIS LINE
CRASHES THE MACRO" in the below code. Amongst the various error msgs I've
received while changing things around, the current code (below) generates a
"Run-time error '13':/Type mismatch" error msg. What the heck am I doing
wrong!?

=====================================================
Option Explicit
Sub SortCurrentSheet()
'
' Sort Macro
' Sorts Timesheet data per project number
'
' Keyboard Shortcut: Ctrl+s

Dim RngName As String

'Following code selects the data to be sorted:
Range("A1").Select
RngName = ActiveSheet.Range("A1").CurrentRegion.Address
ActiveWorkbook.Names.Add Name:="TestName", RefersToR1C1:=RngName

ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=ActiveCell. _
Offset(0, 4).Range(RngName), SortOn:=xlSortOnValues,
Order:=xlAscending, _
DataOption:=xlSortNormal
ActiveSheet.Sort.SortFields.Add Key:=ActiveCell. _
Offset(0, 5).Range(RngName), SortOn:=xlSortOnValues,
Order:=xlAscending, _
DataOption:=xlSortNormal
ActiveSheet.Sort.SortFields.Add Key:=ActiveCell. _
Offset(0, 6).Range(RngName), SortOn:=xlSortOnValues,
Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange RngName 'THIS LINE CRASHES THE MACRO
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

End Sub
=====================================================

Thank you for any assistnace.

Rich

 

[Per Jessen]

Hi

You miss the range statement:

..SetRange Range(RngName)

Regards,
Per

 

[joel]

Try this

Sub SortCurrentSheet()
'
' Sort Macro
' Sorts Timesheet data per project number
'
' Keyboard Shortcut: Ctrl+s

Dim RngName As String

'Following code selects the data to be sorted:
Range("A1").Select
RngName = ActiveSheet.Range("A1").CurrentRegion.Address
ActiveWorkbook.Names.Add Name:="TestName", RefersToR1C1:=RngName

Set SortRange = ActiveSheet.Range("A1").CurrentRegion

SortRange.Sort _
key1:=Range("E1"), _
Order1:=xlAscending, _
key2:=Range("F1"), _
Order2:=x_lAscending, _
key2:=Range("G1"), _
Order2:=xlAscending, _
Header:=xlYes
End Sub

 

[Rich]

The goofy this is that I had tried it that way; when I did, I received an
error on the ".Apply" line, which gave me an "Run-time error '1004':/The sort
reference is not valid. Make sure that it's within the data you want to sort,
and the first Sort By box isn't the same or blank."

I'm figuring that I must be defining and/or stating something incorrectly.

I'm trying a few more things, but the reply from "Joel" worked, with (oddly)
one slight modification.

I'm still new to VBA and don't do it that often to really understand nearly
as much as I need to!

Thanks again!

Rich

 

[Rich]

It worked, Joel . . . but I had to make one modification . . .

It didn't like the variable (even with "Option Explicit" rem'd out!). So I
put replaced the var "SortRange" with the full statement. It worked find.

Thank you.

Rich

 

[joel]

You need to change the last sort order and key from 2 to 3. I copied the
line and forgot to make the change. I don't like the SORT fields method in
VBA. The sort command the way I did it is much simplier.

 

[gimme_this_gimme_that]

With ActiveSheet.Sort
.SetRange "TestRange" ' you must use a range name not an
address
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

 

[Rich]

Thank you . . . good eye for your own work. Will do!