Active Cell - Remember

[Paul Brown]

Hello all,

I have a macro (recorded) which sorts data - approx 1,000 rows. Before the
sort the active cell could be C504 but after the sort the data that was
contained in that cell may have moved to say C11. I'm trying to get the
active cell to move to the new location. Does anyone have any ideas? I was
toying with copying cell contents to clipboard pre-sort and then pasting the
data into the find function - this is straightforward manually but no idea
how to automate. Maybe there's a better way?

I'm grateful for any help.

Paul.

 

[Mike Fogleman]

Sub sorta()
Dim myvalue As String

myvalue = ActiveCell.Value

' Do your sort routine here.
' Example sort line:
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

' The next line will find the original value
Cells.Find(What:=myvalue, SearchOrder:=xlByColumns).Activate
End Sub

Mike F

 

[Guest]

But what if there are duplicate values of myvalue?
Stefi


„Mike Fogleman†ezt írta:

 

[Pete]

Before sorting you could use a blank column and fill this with the
sequence 1,2,3 etc. Include this column in your sort range. Then if the
active cell had been C504, you just need to search for 504 in this
helper column - the column can be deleted when not needed.

Pete

 

[Guest]

Clever!
Regards,
Stefi


„Pete†ezt írta:

 

[Paul Brown]

Thanks all,

Guidance much appreciated.

Paul.

Clever!
Regards,
Stefi


"Pete" ezt írta: