Re: Find and Replace against set of rules in 2nd column

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

I found this, however I need some help with it.

Again, I want to replace a special character "^" with the contents of Column
B.

Option Explicit
Sub testme()

Dim myCell As Range
Dim RngToChange As Range
Dim ValsToFixRng As Range

With Worksheets("Sheet1")
Set RngToChange = .Columns(1)
End With

With Worksheets("Sheet2")
Set ValsToFixRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In ValsToFixRng.Cells
RngToChange.Replace what:=myCell.Value, _
replacement:=myCell.Offset(0, 1).Value, _
lookat:=xlPart, searchorder:=xlByRows, _
MatchCase:=False
Next myCell

End Sub



"Hank Rouse" <hank_top_sf@xxxxxxxxxxx> wrote in message
news:JHKyf.7902$Jd.7676@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> Sorry, you missed the whole point of the loop through.
>
>
>
> "RagDyeR" <ragdyer@xxxxxxxxxxxxx> wrote in message
> news:%231ZktbjGGHA.3896@xxxxxxxxxxxxxxxxxxxxxxx
> > You could make Column C into a "helper" column and enter this in C1:
> >
> > =SUBSTITUTE(A1,"^",B1)
> >
> > And copy down as needed.
> >
> > You could then delete the Text formula in Column C, and leave the data
> > behind by selecting Column C, right click and choose "Copy".
> > Right click again, and choose "PasteSpecial",
> > And click on "Values", then <OK>.
> >
> > --
> >
> > HTH,
> >
> > RD
> > =====================================================
> > Please keep all correspondence within the Group, so all may benefit!
> > =====================================================
> >
> > "Hank Rouse" <hank_top_sf@xxxxxxxxxxx> wrote in message
> > news:bfByf.3038$F01.1160@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> > Looking for a tool, or code that does the following.
> >
> > Replace Special Character in Sheet 1, Column A. [using "^" as my
special
> > character, remove the quotes. Could change if using something else
would
> be
> > better.]
> >
> > Replace ^ with contents of Sheet 1, Column B.
> >
> > For example I have:
> >
> > Column A Column B
> >
> > 12^798 xyz
> > 0^5131 abc
> > 5296^4 efg
> >
> > Column A represents item #'s out of our catalogue.
> > Column B represents Supplier
> >
> > So in the end I would get:
> >
> > Column A Column B
> >
> > 12xyz798 xyz
> > 12abc798 abc
> > 12efg798 efg
> > 0zyx5131 xyz
> > 0abc5131 abc
> > 0efg5131 efg
> > 5296xyz4 xyz
> > 5296abc4 abc
> > 5296efg4 efg
> >
> > Presently there are 59 criteria in Column B and I have to perform this
> > against 19 sites. So doing this manual via Find and Replace takes
> forever.
> >
> > VBA code, or something similar would be great.
> >
> > Thanks in Advance
> >
> >
> >
>
>


.

Relevant Pages