sort and replace macro help

[ace2356]

I am trying to create a macro to sort and replace text and have an example below.

1) First I want to sort everything with column b from lowest to highest and that would sort the other columns with it, so each row keeps the same info
2) Next, replace the text in column D with a new word
- for example, "unlock cherry" will be "c"


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD]unlock cherry[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]apple [/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]4[/TD]
[TD][/TD]
[TD]strawberry[/TD]
[/TR]
</tbody>[/TABLE]

 

[JoeMo]

Assumes data begins in A1 and you want to replace any part of a col D cell that meets your criteria.

Sub Ace()
Const TextToReplace As String = "unlock cherry"
Const ReplcWith As String = "c"
Dim R As Range
Set R = Range("A1:D" & Cells(Rows.Count, "B").End(xlUp).Row)
Application.ScreenUpdating = False
With R
    .Sort key1:=[B1], order1:=xlAscending
    .Columns(4).Replace what:=TextToReplace, replacement:=ReplcWith, lookat:=xlPart
End With
Application.ScreenUpdating = True
End Sub

 

[ace2356]

How about if I want to replace other text as well

 

[JoeMo]

change the constants to arrays.