C
Connie
I have a command button to sort the data below. I would like to sort
the data first by TechNo in ascending order for employees that have a
TechNo, and then by name in ascending order for employees who don't
have a TechNo. When I sort by Key1=TechNo, Ascending,
Key2=EmployeeName, Ascending, the employees with no TechNo show up
first. Note that Tech_No is a text field with leading zeros.
Regardless of whether I choose to sort text as numbers or sort normal,
I get the same result. Any suggestions would be appreciated! Thanks
Following is the code I'm using to sort:
' Sort range
Sheets("Compiled Totals").Select
Set rng = GetRealLastCell(ActiveSheet)
Sheets("Compiled Totals").Range("$A$9:" + rng.Address).sort
Key1:=Sheets("Compiled Totals").Range("D9"), Order1:=xlAscending,
Key2:=Sheets("Compiled Totals").Range( _
"B9"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase _
:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortTextAsNumbers, _
DataOption2:=xlSortNormal
********************************************************************************************************
Function GetRealLastCell(sh As Worksheet) As Range
Dim RealLastRow As Long
Dim RealLastColumn As Long
On Error Resume Next
RealLastRow = _
sh.Cells.Find("*", sh.Range("A1"), , , xlByRows, xlPrevious).Row
RealLastColumn = _
sh.Cells.Find("*", sh.Range("A1"), , , xlByColumns,
xlPrevious).Column
Set GetRealLastCell = sh.Cells(RealLastRow, RealLastColumn)
End Function
*****************************************************************************
Data:
EndDate EmployeeName OracleID TechNo
9/30/2006 Tim Jones 12345 1234
10/1/2006 Tim Jones 12345 1234
10/2/2006 Tim Jones 12345 1234
10/3/2006 Tim Jones 12345 1234
10/4/2006 Tim Jones 12345 1234
10/5/2006 Tim Jones 12345 1234
10/6/2006 Tim Jones 12345 1234
9/30/2006 Jan Clark 34567 0
10/1/2006 Jan Clark 34567 0
10/2/2006 Jan Clark 34567 0
10/3/2006 Jan Clark 34567 0
10/4/2006 Jan Clark 34567 0
10/5/2006 Jan Clark 34567 0
10/6/2006 Jan Clark 34567 0
9/30/2006 Joe Hall 34566 2345
10/1/2006 Joe Hall 34566 2345
10/2/2006 Joe Hall 34566 2345
10/3/2006 Joe Hall 34566 2345
10/4/2006 Joe Hall 34566 2345
10/5/2006 Joe Hall 34566 2345
10/6/2006 Joe Hall 34566 2345
9/30/2006 Sally Smith 12345 9876
10/1/2006 Sally Smith 12345 9876
10/2/2006 Sally Smith 12345 9876
10/3/2006 Sally Smith 12345 9876
10/4/2006 Sally Smith 12345 9876
10/5/2006 Sally Smith 12345 9876
10/6/2006 Sally Smith 12345 9876
9/30/2006 John Doe 12345 0654
10/1/2006 John Doe 12345 0654
10/2/2006 John Doe 12345 0654
10/3/2006 John Doe 12345 0654
10/4/2006 John Doe 12345 0654
10/5/2006 John Doe 12345 0654
10/6/2006 John Doe 12345 0654
9/30/2006 Tom Hanks 12345 0345
10/1/2006 Tom Hanks 12345 0345
10/2/2006 Tom Hanks 12345 0345
10/3/2006 Tom Hanks 12345 0345
10/4/2006 Tom Hanks 12345 0345
10/5/2006 Tom Hanks 12345 0345
10/6/2006 Tom Hanks 12345 0345
9/30/2006 Cheryl Ladd 12345 0
10/1/2006 Cheryl Ladd 12345 0
10/2/2006 Cheryl Ladd 12345 0
10/3/2006 Cheryl Ladd 12345 0
10/4/2006 Cheryl Ladd 12345 0
10/5/2006 Cheryl Ladd 12345 0
10/6/2006 Cheryl Ladd 12345 0
the data first by TechNo in ascending order for employees that have a
TechNo, and then by name in ascending order for employees who don't
have a TechNo. When I sort by Key1=TechNo, Ascending,
Key2=EmployeeName, Ascending, the employees with no TechNo show up
first. Note that Tech_No is a text field with leading zeros.
Regardless of whether I choose to sort text as numbers or sort normal,
I get the same result. Any suggestions would be appreciated! Thanks
Following is the code I'm using to sort:
' Sort range
Sheets("Compiled Totals").Select
Set rng = GetRealLastCell(ActiveSheet)
Sheets("Compiled Totals").Range("$A$9:" + rng.Address).sort
Key1:=Sheets("Compiled Totals").Range("D9"), Order1:=xlAscending,
Key2:=Sheets("Compiled Totals").Range( _
"B9"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase _
:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortTextAsNumbers, _
DataOption2:=xlSortNormal
********************************************************************************************************
Function GetRealLastCell(sh As Worksheet) As Range
Dim RealLastRow As Long
Dim RealLastColumn As Long
On Error Resume Next
RealLastRow = _
sh.Cells.Find("*", sh.Range("A1"), , , xlByRows, xlPrevious).Row
RealLastColumn = _
sh.Cells.Find("*", sh.Range("A1"), , , xlByColumns,
xlPrevious).Column
Set GetRealLastCell = sh.Cells(RealLastRow, RealLastColumn)
End Function
*****************************************************************************
Data:
EndDate EmployeeName OracleID TechNo
9/30/2006 Tim Jones 12345 1234
10/1/2006 Tim Jones 12345 1234
10/2/2006 Tim Jones 12345 1234
10/3/2006 Tim Jones 12345 1234
10/4/2006 Tim Jones 12345 1234
10/5/2006 Tim Jones 12345 1234
10/6/2006 Tim Jones 12345 1234
9/30/2006 Jan Clark 34567 0
10/1/2006 Jan Clark 34567 0
10/2/2006 Jan Clark 34567 0
10/3/2006 Jan Clark 34567 0
10/4/2006 Jan Clark 34567 0
10/5/2006 Jan Clark 34567 0
10/6/2006 Jan Clark 34567 0
9/30/2006 Joe Hall 34566 2345
10/1/2006 Joe Hall 34566 2345
10/2/2006 Joe Hall 34566 2345
10/3/2006 Joe Hall 34566 2345
10/4/2006 Joe Hall 34566 2345
10/5/2006 Joe Hall 34566 2345
10/6/2006 Joe Hall 34566 2345
9/30/2006 Sally Smith 12345 9876
10/1/2006 Sally Smith 12345 9876
10/2/2006 Sally Smith 12345 9876
10/3/2006 Sally Smith 12345 9876
10/4/2006 Sally Smith 12345 9876
10/5/2006 Sally Smith 12345 9876
10/6/2006 Sally Smith 12345 9876
9/30/2006 John Doe 12345 0654
10/1/2006 John Doe 12345 0654
10/2/2006 John Doe 12345 0654
10/3/2006 John Doe 12345 0654
10/4/2006 John Doe 12345 0654
10/5/2006 John Doe 12345 0654
10/6/2006 John Doe 12345 0654
9/30/2006 Tom Hanks 12345 0345
10/1/2006 Tom Hanks 12345 0345
10/2/2006 Tom Hanks 12345 0345
10/3/2006 Tom Hanks 12345 0345
10/4/2006 Tom Hanks 12345 0345
10/5/2006 Tom Hanks 12345 0345
10/6/2006 Tom Hanks 12345 0345
9/30/2006 Cheryl Ladd 12345 0
10/1/2006 Cheryl Ladd 12345 0
10/2/2006 Cheryl Ladd 12345 0
10/3/2006 Cheryl Ladd 12345 0
10/4/2006 Cheryl Ladd 12345 0
10/5/2006 Cheryl Ladd 12345 0
10/6/2006 Cheryl Ladd 12345 0