multiple if/then replace

dvahon

New Member
Joined
Nov 29, 2006
Messages
18
I have about 50 words I need to replace with a shortened version of that word. For example, Amethyst to AM, Aqua to AQ, Black to BLK etc. I will need to do this a few times over the next few weeks to new data. I've never used VBA but I'm excellent at copying, pasting and running :) Can someone help me by supplying the code?
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
If you create a list of the words and there corresponding replacement then you can loop through them using the Replace function

Code:
Sub Macro1()
Dim NumReps As Long
Dim FindVal As String
Dim RepVal As String
NumReps = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To NumReps
    FindVal = Sheet1.Range("A" & i).Value
    RepVal = Sheet1.Range("B" & i).Value
    Sheet2.Cells.Replace What:=FindVal, Replacement:=RepVal
Next i
End Sub

In this case the list is on Sheet1 and I was only looking to replace values on Sheet2. You can alter this to suit.

HTH


Dave
 
Upvote 0
Hi Dave,

Is there a way to force it to find an exact match only? Because my column A can have something like "Crystal" (for which column B has CR) and something like "Crystal AB" (for which column B has CAB) and something like "Crystal Shadow" (for which column B has CS).

The way it's working right now it's turning everything that says Crystal into CR. So Crystal Shadow becomes CR Shadow instead of CS.

Thanks again
 
Upvote 0
Change it to:

Code:
Sheet2.Cells.Replace What:=FindVal, Replacement:=RepVal, Lookat:=xlWhole, MatchCase:=True

Troy
 
Upvote 0
I'm now finding many other uses for this macro. Is there a way to also change the cell fill color (only for the cells that were changed)?
 
Upvote 0
Sure


Rich (BB code):
Sub Macro1()
Dim NumReps As Long
Dim FindVal As String
Dim RepVal As String
Application.ReplaceFormat.Interior.Color = 255
NumReps = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To NumReps
    FindVal = Sheet1.Range("A" & i).Value
    RepVal = Sheet1.Range("B" & i).Value
    Sheet2.Cells.Replace What:=FindVal, Replacement:=RepVal, Lookat:=xlWhole, MatchCase:=True, ReplaceFormat:=True
Next i
End Sub
 
Upvote 0
Wow, I've been looking and working on something like this for a while using selection.replace. Is there a way to make your code replace text in a selection rather than on "sheet2"?

FYI, my code is below and didn't seem to work unless everything was in the right order:

Sub list_replace()
Dim S
For Each S In Worksheets("LIST09").Range("A1", Range("A2153").End(xlUp).Address)
Selection.Replace What:=S.Text, Replacement:=S.Offset(0, 1).Text, LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=True
Next S
End Sub

Thanks!
 
Upvote 0

Forum statistics

Threads
1,212,938
Messages
6,110,775
Members
448,298
Latest member
carmadgar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top