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

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,022
Office Version
  1. 365
  2. 2016
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
 

dvahon

New Member
Joined
Nov 29, 2006
Messages
18
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
 

9ballpimp

Board Regular
Joined
Jan 26, 2005
Messages
141

ADVERTISEMENT

Change it to:

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

Troy
 

dvahon

New Member
Joined
Nov 29, 2006
Messages
18

ADVERTISEMENT

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)?
 

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,022
Office Version
  1. 365
  2. 2016
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
 

colinlee

New Member
Joined
Mar 9, 2005
Messages
9
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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,856
Messages
5,766,786
Members
425,378
Latest member
kapoor2892

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
Top