Combo Box

[Guest]

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

 

[Guest]

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

 

[Dave Peterson]

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.)

 

[Guest]

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"),False)

HTH
Patrick

 

[Guest]

Thanks All- this is great

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"),False)

HTH
Patrick