Find and replace in string using values from another column

[Mike_Gr]

I need to find and replace text in column A by matching the values in Column B with part of the string in the cells in column A. When there is a match in Column A this value should be replaced by the text in Column C. What I need is a VBA macro that can search for all the column B-values in column A, and then replace them there with the values in Column C. The result would be "John; Ringo" in A2 and so on.

I would be very grateful for information on how to do this, I'm a complete newbie when it comes to VBA. (I'm using Excel 2010 and Windows 7)



[TABLE="width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[/TR]
[TR]
[TD]abc-123; efg-456[/TD]
[TD]abc-123[/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD]bcd-234[/TD]
[TD]bcd-234[/TD]
[TD]Paul[/TD]
[/TR]
[TR]
[TD]cde-345[/TD]
[TD]cde-345[/TD]
[TD]George[/TD]
[/TR]
[TR]
[TD]efg-456; bcd-234; bcd-23[/TD]
[TD]efg-456[/TD]
[TD]Ringo[/TD]
[/TR]
</tbody>[/TABLE]

 

[JLGWhiz]

see if this works:

Code:
Sub rplc()
Dim sh As Worksheet, lr As Long, rng As Range
Set sh = Sheets(1) 'Edit sheet name
lr = sh.Cells(Rows.Count, 2).End(xlUp).Row
Set rng = sh.Range("B2:B" & lr)
For Each c In rng
sh.Range("A:A").Replace What:=c.Value, Replacement:=c.Offset(0, 1).Value, LookAt:=xlPart, MatchCase:=False
Next
End Sub
Code:

 

[Mike_Gr]

Thanks a lot, but it doesn't quite do the trick. Actually nothing happens at all when I run the script...
Should I change anything except the name of the worksheet?

 

[Mike_Gr]

Shouldn't the "Replace What" and "Replacement" be changed to b. instead of c.?

sh.Range("A:A").Replace What:=c.Value, Replacement:=c.Offset(0, 1).Value, LookAt:=xlPart, MatchCase:=False

However, when changed it I got an errror instead (nr 424 "Object needed").

 

[JLGWhiz]

Shouldn't the "Replace What" and "Replacement" be changed to b. instead of c.?

sh.Range("A:A").Replace What:=c.Value, Replacement:=c.Offset(0, 1).Value, LookAt:=xlPart, MatchCase:=False

However, when changed it I got an errror instead (nr 424 "Object needed").

The code was correct as written and tested based on the original post.
Here is how it works. Sheets(1) was assumed for the worksheet. If that is wrong, you need to change it to the correct sheet name.

Code:
Sub rplc()
'Declare variables with Dim statement
Dim sh As Worksheet, lr As Long, rng As Range, c As Range
'Assign values to the variables
Set sh = Sheets(1) 'Edit sheet name
lr = sh.Cells(Rows.Count, 2).End(xlUp).Row 'Finds limit of range in column B
Set rng = sh.Range("B2:B" & lr) 'Assigns column B range to object variable
For Each c In rng 'Creates object variable representing a cell in column B
'The following replaces any item in column A with the value of the cell in column C
'if it matches the cell in column B.
sh.Range("A:A").Replace What:=c.Value, Replacement:=c.Offset(0, 1).Value, LookAt:=xlPart, MatchCase:=False
Next 'Goes to next cell in column B until range limit is reached.
End Sub
Code:

I don't know if the commas versus the semicolons madke a difference. You might need to change those.

 

[Mike_Gr]

Thank you so much for your patience! I got it working now. I made a mistake in setting the name for the worksheet but now it works like a charm.

 

[JLGWhiz]

Thank you so much for your patience! I got it working now. I made a mistake in setting the name for the worksheet but now it works like a charm.

glad you got it going.

Regards, JLG

 

[zouzougr]

Hello JLG,

I am looking for a code which is pretty similar to what you have provided here,
but my dilemma is that I need to replace the "whole cell" in column A by the relevant cell in Column C in case of a partial match is made from the B column.

So in short, it's the same process that exists in your code but I need to replace the "whole" cell by another relevant cell, and not just the matched part of it.

Any help would me much appreciated,
Many Thanks,

Joseph

 

[zouzougr]

If the Replace line had "What" setting a value somewhat like :
c.value.(some sort of wild card) ,

This would replace the whole content of the cell since especially my matches are part of the first/left portion of the cell content.

 

[zouzougr]

Thanks, I figured it out :
What:=(c.Value & "*")