VBA Help Please
 

The code below looks for a text string in column D: "AV1 Swb"
then offsets to a desired position and adds text: "Switchboard"

I need to make a modification to this, but am not sure how. Instead of
offsetting text, I want to replace the entire entry that contains "AV1 Swb"
with "Switchboard".

Any help you can provide would be appreciated.

Here is the code I have today in a large macro:

Set rng = Columns(4).Find(what:="AV1 Swb", _
after:=Range("D1"), LookIn:=xlFormulas, _
lookat:=xlPart, searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
fAddr = rng.Address
Do
rng.Offset(0, -1).Value = "Switchboard"
Set rng = Columns(4).FindNext(rng)
Loop While rng.Address < fAddr
End If

VBA Help Please
 

Untested:

Do
rng.Value = "Switchboard"
Set rng = Columns(4).FindNext(rng)
if rng is nothing then exit do
Loop

But I think I would drop the loop and just do an edit|replace in code:

Columns("D:D").Replace What:="*AV1 Swb*", Replacement:="Switchboard", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False



Scott Wagner wrote:

The code below looks for a text string in column D: "AV1 Swb"
then offsets to a desired position and adds text: "Switchboard"

I need to make a modification to this, but am not sure how. Instead of
offsetting text, I want to replace the entire entry that contains "AV1 Swb"
with "Switchboard".

Any help you can provide would be appreciated.

Here is the code I have today in a large macro:

Set rng = Columns(4).Find(what:="AV1 Swb", _
after:=Range("D1"), LookIn:=xlFormulas, _
lookat:=xlPart, searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
fAddr = rng.Address
Do
rng.Offset(0, -1).Value = "Switchboard"
Set rng = Columns(4).FindNext(rng)
Loop While rng.Address < fAddr
End If

--

Dave Peterson

VBA Help Please
 

Thank you sir! That worked perfectly. (as always)