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.

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
"Paul Brown" wrote in message
...
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.

But what if there are duplicate values of myvalue?
Stefi


€žMike Fogleman€� ezt Ã*rta:

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
"Paul Brown" wrote in message
...
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.

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

Clever!
Regards,
Stefi


€žPete€� ezt Ã*rta:

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

Thanks all,

Guidance much appreciated.

Paul.

"Stefi" wrote in message
...
Clever!
Regards,
Stefi


"Pete" ezt írta:

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