| Home |
| Search |
| Today's Posts |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bombing on final sort command . . .
Thank you . . . good eye for your own work. Will do!
"joel" wrote: 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. "Rich" wrote: 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" wrote: 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" wrote: 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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I have a Table that I can't Sort--Final attempt for help | Excel Discussion (Misc queries) | |||
| VBA bombing out on this line | Excel Programming | |||
| Code bombing on Loop While line | Excel Programming | |||
| Macro bombing out | Excel Programming | |||
| Macro Bombing out - Leith Ross | Excel Programming | |||
Threaded Mode
