Find and Replace against set of rules in 2nd table/worksheet

[Hank Rouse]

Looking for a tool, or code that does the following.

Edit [partial cell data] in Sheet1, if [partial cell data] meets criteria in
Sheet2, COL1. Sheet2, COL2 has the REPLACE WITH information.

Presently there are 59 criteria on Sheet 2, 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.

(Wanted to write an Update Query Access, but Access sent me here.)

Thanks in Advance

 

[Hank Rouse]

TBL1

COL1
Mari Johnson
Jim Evans
Charles Stanley
Jerry Straight

TBL2

COL1 COL2
Mari Mary
Straight Strait

See, I'm simply searching TBL1, COL1 for any match in TBL2, COL1.
If a match is found, Bad data in TBL1, COL1 is replaced by TBL2, COL2

 

[Dave Peterson]

One way would be to cycle through column A of table 2 and just do Edit|replace
with the values in column A and column B.

I put each table on its own sheet and ran this:

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

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

TBL1

COL1
Mari Johnson
Jim Evans
Charles Stanley
Jerry Straight

TBL2

COL1 COL2
Mari Mary
Straight Strait

See, I'm simply searching TBL1, COL1 for any match in TBL2, COL1.
If a match is found, Bad data in TBL1, COL1 is replaced by TBL2, COL2

Looking for a tool, or code that does the following.

Edit [partial cell data] in Sheet1, if [partial cell data] meets criteria in
Sheet2, COL1. Sheet2, COL2 has the REPLACE WITH information.

Presently there are 59 criteria on Sheet 2, 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.

(Wanted to write an Update Query Access, but Access sent me here.)

Thanks in Advance

 

[mkwan23]

This is very useful however I'm trying to replace only items in a
certain column.

I thought I'd try to change:


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


But that didn't work.

The code, as it is, ends up replacing everything that had a match in
the entire worksheet. What would I need to change in order to only
find and replace cells in a certain column? Your reply is greatly
appreciated, thanks.

 

[Dave Peterson]

I think the easiest change would be:

Set RngToChange = .Columns(2)

But you could do things like:

Set RngToChange = .Range("B:B")
or
Set RngToChange = .Range("B1").entirecolumn

Whichever makes it easier when you're reading the code later.

==

I don't understand why that code would change more stuff than what's in the
RngToChange, though.

So save your workbook before you rerun it--just in case.

 

[mkwan23]

Worked perfect.....Thanks much!!!

 

[mkwan23]

By the way, it might have changed everything because I changed your
code:

lookat:=xlPart

to

lookat:=xlWhole


the xlPart was only matching partials and I needed to match exactly.

 

[Dave Peterson]

Glad you got it working the way you wanted.

By the way, it might have changed everything because I changed your
code:

lookat:=xlPart

to

lookat:=xlWhole

the xlPart was only matching partials and I needed to match exactly.