|
Sort Error
The following code is designed to sort an alphanumeric list consisting of
text & one or 2 digits by inserting a leading zero, sorting, and then stripping the zero back, to produce the following results: Pre-sort condition -------------------- AC20 AC1 AC2 AC9 AC10 Normal Excel Sort Result ----------------------------- AC1 AC10 AC2 AC20 AC9 Macro Sort Result --------------------- AC1 AC2 AC9 AC10 AC20 The Selection.Sort line in the following code generates the error €œSort method of Range class failed.€ Does anyone know why? Sub AlphaSort() Dim rng As Range Dim intFirst As Integer Dim intLast As Integer ' Sorts Alphanumeric list by adding then removing leading zeros Call AddLeadingZero intFirst = Selection(1).Row intLast = Selection(Selection.Count).Row Set rng = Range(Cells(intFirst, "B"), Cells(intLast, "F")) rng.Select Selection.Sort 'Code fails here Call RemoveLeadingZero End Sub Thank you. Sprinks |
Sort Error
A couple of things. you don't need the select. Since you have a range object
you can just sort it, but you have to specify a key. My guess would be by the cell intfirst and column B, but that is up to you. Record a macro to see all of the possible properties that you can set. Heading will probably be xlYes and there may be a few other things you want to set. rng.sort Key1:=???, ... -- HTH... Jim Thomlinson "Sprinks" wrote: The following code is designed to sort an alphanumeric list consisting of text & one or 2 digits by inserting a leading zero, sorting, and then stripping the zero back, to produce the following results: Pre-sort condition -------------------- AC20 AC1 AC2 AC9 AC10 Normal Excel Sort Result ----------------------------- AC1 AC10 AC2 AC20 AC9 Macro Sort Result --------------------- AC1 AC2 AC9 AC10 AC20 The Selection.Sort line in the following code generates the error €œSort method of Range class failed.€ Does anyone know why? Sub AlphaSort() Dim rng As Range Dim intFirst As Integer Dim intLast As Integer ' Sorts Alphanumeric list by adding then removing leading zeros Call AddLeadingZero intFirst = Selection(1).Row intLast = Selection(Selection.Count).Row Set rng = Range(Cells(intFirst, "B"), Cells(intLast, "F")) rng.Select Selection.Sort 'Code fails here Call RemoveLeadingZero End Sub Thank you. Sprinks |
Sort Error
Thanks, Jim, it works flawlessly now.
Sprinks "Jim Thomlinson" wrote: A couple of things. you don't need the select. Since you have a range object you can just sort it, but you have to specify a key. My guess would be by the cell intfirst and column B, but that is up to you. Record a macro to see all of the possible properties that you can set. Heading will probably be xlYes and there may be a few other things you want to set. rng.sort Key1:=???, ... -- HTH... Jim Thomlinson "Sprinks" wrote: The following code is designed to sort an alphanumeric list consisting of text & one or 2 digits by inserting a leading zero, sorting, and then stripping the zero back, to produce the following results: Pre-sort condition -------------------- AC20 AC1 AC2 AC9 AC10 Normal Excel Sort Result ----------------------------- AC1 AC10 AC2 AC20 AC9 Macro Sort Result --------------------- AC1 AC2 AC9 AC10 AC20 The Selection.Sort line in the following code generates the error €œSort method of Range class failed.€ Does anyone know why? Sub AlphaSort() Dim rng As Range Dim intFirst As Integer Dim intLast As Integer ' Sorts Alphanumeric list by adding then removing leading zeros Call AddLeadingZero intFirst = Selection(1).Row intLast = Selection(Selection.Count).Row Set rng = Range(Cells(intFirst, "B"), Cells(intLast, "F")) rng.Select Selection.Sort 'Code fails here Call RemoveLeadingZero End Sub Thank you. Sprinks |
| All times are GMT +1. The time now is 01:34 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com