PDA

View Full Version : Search for a string and then sort by that column



qcoleman
09-30-2011, 02:07 PM
I'm tyring to find a way of searching for a specific string in a row and once that string is found I need to sort by that string in VBA.

mancubus
09-30-2011, 03:23 PM
hi.
you may play around with this


Sub Find_Sort()

Dim CellFound As Range, rng2sort As Range
Dim StrFind As String
Dim ColFound As Long


With ActiveSheet 'change to suit
On Error Resume Next

StrFind = "Weekend" 'change to suit

Set CellFound = .Cells.Find(What:=StrFind, After:=.Cells(1, 1), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

On Error GoTo 0

If Not CellFound Is Nothing Then
ColFound = CellFound.Column
Set rng2sort = CellFound.CurrentRegion
End If

rng2sort.Sort key1:=Columns(ColFound), order1:=xlAscending, Header:=xlYes
End With

End Sub