Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo Box
Sub SOR()
I have a combobox - when I choose an option it arranges data according to Client which is currently in column G, however I need to sort data according to "Client" irrespective of the Column, how do I tinker with the code below to do this - Thanks The bit I think I need changing is Key1:=Range("G9") to Range("col("Client")9) or something like this Range("C9:AS69").Select Selection.Sort Key1:=Range("G9"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo Box
Here is some code for you It looks up the word "Client" in the range that you
want to sort, and then sorts that range by that column. If Client is not found in the 9th row then an error message is posted... I hope this is what you wanted... Const strSortHeading As String = "Client" Sub SortBy() Dim rngSortColumn As Range Dim rngToSort As Range Dim wks As Worksheet Set wks = ActiveSheet Set rngToSort = wks.Range("C9:AS69") Set rngSortColumn = Intersect(rngToSort, wks.Rows(9).Find(strSortHeading)) If rngSortColumn Is Nothing Then MsgBox strSortHeading & " column not found", vbCritical, "Sort Error" Else rngToSort.Sort Key1:=rngSortColumn, Order1:=xlAscending, Header:=xlYes End If Set rngSortColumn = Nothing Set wks = Nothing End Sub HTH "teresa" wrote: Sub SOR() I have a combobox - when I choose an option it arranges data according to Client which is currently in column G, however I need to sort data according to "Client" irrespective of the Column, how do I tinker with the code below to do this - Thanks The bit I think I need changing is Key1:=Range("G9") to Range("col("Client")9) or something like this Range("C9:AS69").Select Selection.Sort Key1:=Range("G9"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo Box
dim FoundCell as range
dim FindWhat as string findwhat = "Client" with worksheets("sheet1") with .range("c9:AS9") 'what row has Client in it? Set FoundCell = .Cells.Find(what:=FindWhat, after:=.cells(.cells.count), _ LookIn:=xlFormulas, lookat:=xlWhole, _ searchorder:=xlByRows, searchdirection:=xlNext) end with if foundcell is nothing then 'what do you do if Client can't be found? else .Range("C9:AS69").Sort Key1:=.cells(1,foundcell.column), _ Order1:=xlAscending, _ Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom end if end with (I don't like xlGuess. If you know what it is, I think I'd specify.) teresa wrote: Sub SOR() I have a combobox - when I choose an option it arranges data according to Client which is currently in column G, however I need to sort data according to "Client" irrespective of the Column, how do I tinker with the code below to do this - Thanks The bit I think I need changing is Key1:=Range("G9") to Range("col("Client")9) or something like this Range("C9:AS69").Select Selection.Sort Key1:=Range("G9"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End Sub -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo Box
you're correct
use dim colClient as long colClient = 7 ' 7=G Key1:=Cells(9,colClient) you could easily use say the MATCH() function to determine what column to use eg colClient = worksheet.functions.Match(what,Range("A9:L9"),Fals e) HTH Patrick "teresa" wrote: Sub SOR() I have a combobox - when I choose an option it arranges data according to Client which is currently in column G, however I need to sort data according to "Client" irrespective of the Column, how do I tinker with the code below to do this - Thanks The bit I think I need changing is Key1:=Range("G9") to Range("col("Client")9) or something like this Range("C9:AS69").Select Selection.Sort Key1:=Range("G9"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo Box
Thanks All- this is great
"Patrick Molloy" wrote: you're correct use dim colClient as long colClient = 7 ' 7=G Key1:=Cells(9,colClient) you could easily use say the MATCH() function to determine what column to use eg colClient = worksheet.functions.Match(what,Range("A9:L9"),Fals e) HTH Patrick "teresa" wrote: Sub SOR() I have a combobox - when I choose an option it arranges data according to Client which is currently in column G, however I need to sort data according to "Client" irrespective of the Column, how do I tinker with the code below to do this - Thanks The bit I think I need changing is Key1:=Range("G9") to Range("col("Client")9) or something like this Range("C9:AS69").Select Selection.Sort Key1:=Range("G9"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
linking a form combo box... results from the combo box to another | Excel Discussion (Misc queries) | |||
combo reference on another combo box for picking address etc. | Excel Worksheet Functions | |||
"Combo Box - getting control combo box to stick in place in worksh | Excel Discussion (Misc queries) | |||
"Combo Box - getting control combo box to stick in place in worksh | Excel Discussion (Misc queries) | |||
Combo Box - Hide Combo Box w/Check Box | Excel Programming |