Help Please - last bit of code needed

[Guest]

I have the following code in a my worksheet-

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("B1:B126", "D1126"), Target) Is
Nothing Then
Range("G" & ActiveCell.Offset(-1, 0).Row & ":S" & ActiveCell.Offset(-1,
0).Row).Select
Selection.Replace What:="L??O???", _
Replacement:=Cells(ActiveCell.Row, "E").Value, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
End If

End Sub

It does exactly what I want it to do apart from at the end of this sub the
range specified is left selected. So there is one last step that I would
like to perform

Basically if you enter a value into B3 or D3 and hit enter to go to B3/D4
the code then selects the range G:S in the row above and carries out the find
and replace.The Active cell is then G in which ever row the code dictated (I
think)
All fine there ! What I would then like to happen is the active cell (or
selected Cell) to go back to D4 (or B4 if that cell was changed).
I've tried things like -

End If
Then
Range(ActiveCell.Offset(1,-3)).Select
End Sub

and adding a seperate sub

Private Sub Worksheet()
Range(ActiveCell.Offset(1, -3)).Select
End Sub

But with no luck. with the second sub, the first one doesn't work.

Could anyone give me some pointers on where I'm going wrong please. Its
probably something simple but hey! thats me all over.

Thanks in advance.

 

[Don Guillett]

from the vba help for replace. Modify to your needs. NO
selections.Worksheets("Sheet1").Columns("A").Replace _
What:="SIN", Replacement:="COS", _
SearchOrder:=xlByColumns, MatchCase:=True

 

[Guest]

Target is the cell that was originally changed so you can return to it with
the line

Target.Select

 

[Guest]

Hi,
As simple as taking the range out???
ActiveCell.Offset(1, -3).Select

peter

 

[Guest]

Thanks Jim that work a dream. I added Offset(1,0) to finish the job of nicely.

Don, thanks for you response but I don't have a clue what you your answer
meant. Sorry I a bit of a dunce at VBA, I don't get were the sin ans cos
factor into things. for that matter I don't even know what they
mean!!!!!!!!!!!!

Peter, Thanks for your Reply too. That worked as well. Thought it would be
something simple(°|°)

Thanks to you you all for a very swift response.