MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Finding and replacing the cell next to a cell with a certain string

  Asked By: Daisy    Date: Aug 27    Category: MS Office    Views: 1290

The following code snippet will replace TextToReplace with
ReplacementText in an Excel worksheet:

Cells.Replace What:=TextToReplace, Replacement:=ReplacementText,
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _

That's easy enough. I'm a little stumped by this next one though...

I want to replace the text in the cell TO THE LEFT of the cell
containing the string TextToReplace with something else, which I'll
call TextDescription.

How do I replace text in the cell NEXT to a cell containing a
particular string?



2 Answers Found

Answer #1    Answered By: Joyce Edwards     Answered On: Aug 27

This works, replace  my "gggg" etc. with your variables. Tere are no
checks to see whether it finds something in the first column, when it
would have difficulty putting anything in the cell  to the left!:

Sub Blah()
Set c = Cells.Find(What:="gggg", LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False)
If Not c Is Nothing Then
c.Replace What:="gggg", Replacement:="aaaa", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
c.Offset(0, -1) = "6Descriptive text"
Set c = Cells.FindNext(c)
Loop While Not c Is Nothing
End If
End Sub

Answer #2    Answered By: Adel Fischer     Answered On: Aug 27

I had to ditch the loop because it gets stuck and goes around for
ever, but without the loop it works fine. A couple of the offset
replacements don't seem to want to replace  for some strange reason,
but your code  was a huge help and I think I can take it from here.