Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bombing on final sort command . . .
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bombing on final sort command . . .
Hi
You miss the range statement: ..SetRange Range(RngName) Regards, Per "Rich" skrev i meddelelsen ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bombing on final sort command . . .
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bombing on final sort command . . .
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 "Per Jessen" wrote: Hi You miss the range statement: ..SetRange Range(RngName) Regards, Per "Rich" skrev i meddelelsen ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bombing on final sort command . . .
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bombing on final sort command . . .
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bombing on final sort command . . .
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 |
#8
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 |
Reply |
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 |