Logo 
Search:

MS Office Answers

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds
  Question Asked By: Mona Mehta   on Mar 02 In MS Office Category.

  
Question Answered By: Viola Hanson   on Mar 02

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.

Share: 

 

This Question has 9 more answer(s). View Complete Question Thread

 
Didn't find what you were looking for? Find more on Retain cell selection through sort Or get search suggestion and latest updates.


Tagged: