this didn't QUITE do it since the content is non unique,
but gave me the key (pun) for my solution: Stuff unique data into the
selected cell and search for that. Like so:
Sub Sort_n_Select()
'
' Sort_n_Select Macro
' macro recorded (and edited) 10/21/04 by Steve Noskowicz
'
' This macro has the selected cell follow along with a sort.
'
Dim SelectedCellContents As Variant
'
Application.ScreenUpdating = False ' Suspend screen updating
SelectedCellContents = ActiveCell.Value ' Hold the selected
cell's contents
SelectedColumn = ActiveCell.Column ' Note the selected cell's
column
ActiveCell.Value = "ZUJQRX" ' Insert a search key in the selected
cell. If you're a ham and Navy, you'll recognize this key (:-)
'
'=========== Example sort on col D to move the selected cell.======
Rows("5:13").Select ' Select the database
Selection.Sort Key1:=Range("D5"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
'================================================= end sort =======
'
Columns(SelectedColumn).Select ' Select the "Key" Column
'
' Now find the Keyed cell
Selection.Find(What:="ZUJQRX", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=True).Activate
ActiveCell.Select 'Select the found cell
ActiveCell.FormulaR1C1 = SelectedCellContents ' Restore the
content.
Application.ScreenUpdating = True 'Resume screen updating
End Sub
=================
AND it doesn't error out if the selected cell isn't within the sorted
area.