Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
A Sorting Problem
In Excel 2007, I'm trying to sort on column A, with headers of :x: and "y".
There is a formula in column A: =IF(B2="","",B2), then copied down. I need to have Rows 2-4 (cells that don't display anything, but have a formula in them) to sort below the cells which display characters. Does anyone have a suggestion on how to accomplish this? I am using a command button to initiate the sort - the VBA code for the command button is shown below the spreadsheet. A B 1 y z 2 3 4 5 a a 6 c c 7 m m 8 r r 9 x x -------------------- Private Sub CommandButton1_Click() ' Application.Goto Reference:="data" ActiveWorkbook.Worksheets("data").Sort.SortFields. Clear ActiveWorkbook.Worksheets("data").Sort.SortFields. Add Key:=Range( _ "A2:A20"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("data").Sort .SetRange Range("A1:B20") .Header = xltrue .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
A Sorting Problem
Hi
It worked fine for me when I modified to Private Sub CommandButton1_Click() ' ActiveWorkbook.Worksheets("data").Sort.SortFields. Clear ActiveWorkbook.Worksheets("data").Sort.SortFields. Add Key:=Range( _ "A2:A20"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("data").Sort .SetRange Range("A1:B20") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub xlTrue is not valid it should be xlYes -- Regards Roger Govier "k1ngr" wrote in message ... In Excel 2007, I'm trying to sort on column A, with headers of :x: and "y". There is a formula in column A: =IF(B2="","",B2), then copied down. I need to have Rows 2-4 (cells that don't display anything, but have a formula in them) to sort below the cells which display characters. Does anyone have a suggestion on how to accomplish this? I am using a command button to initiate the sort - the VBA code for the command button is shown below the spreadsheet. A B 1 y z 2 3 4 5 a a 6 c c 7 m m 8 r r 9 x x -------------------- Private Sub CommandButton1_Click() ' Application.Goto Reference:="data" ActiveWorkbook.Worksheets("data").Sort.SortFields. Clear ActiveWorkbook.Worksheets("data").Sort.SortFields. Add Key:=Range( _ "A2:A20"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("data").Sort .SetRange Range("A1:B20") .Header = xltrue .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
A Sorting Problem
Unqualified ranges will refer to the active sheet if the code is in a general
module. Unqualified ranges will refer to the worksheet that holds the code if the code is in a worksheet module. And your _Click event looks like the code is in a worksheet module. So qualify all your ranges: Private Sub CommandButton1_Click() Application.Goto Reference:="data" ActiveWorkbook.Worksheets("data").Sort.SortFields. Clear ActiveWorkbook.Worksheets("data").Sort.SortFields. Add _ Key:=ActiveWorkbook.Worksheets("data").Range( _ "A2:A20"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("data").Sort .SetRange ActiveWorkbook.Worksheets("data").Range("A1:B20") .Header = xltrue .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub You may want to try it without the application.goto: Private Sub CommandButton1_Click() with ActiveWorkbook.Worksheets("data") .Sort.SortFields.Clear .Sort.SortFields.Add _ Key:=.Range("A2:A20"), SortOn:=xlSortOnValues, _ Order:=xlAscending, DataOption:=xlSortNormal With .Sort .SetRange .Range("A1:B20") .Header = xltrue .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With end with End Sub (uncompiled, untested.) k1ngr wrote: In Excel 2007, I'm trying to sort on column A, with headers of :x: and "y". There is a formula in column A: =IF(B2="","",B2), then copied down. I need to have Rows 2-4 (cells that don't display anything, but have a formula in them) to sort below the cells which display characters. Does anyone have a suggestion on how to accomplish this? I am using a command button to initiate the sort - the VBA code for the command button is shown below the spreadsheet. A B 1 y z 2 3 4 5 a a 6 c c 7 m m 8 r r 9 x x -------------------- Private Sub CommandButton1_Click() ' Application.Goto Reference:="data" ActiveWorkbook.Worksheets("data").Sort.SortFields. Clear ActiveWorkbook.Worksheets("data").Sort.SortFields. Add Key:=Range( _ "A2:A20"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("data").Sort .SetRange Range("A1:B20") .Header = xltrue .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting VBA problem | Excel Discussion (Misc queries) | |||
sorting problem | Excel Discussion (Misc queries) | |||
SORTING PROBLEM | Excel Worksheet Functions | |||
Sorting problem | Excel Discussion (Misc queries) | |||
Sorting problem | Excel Worksheet Functions |