Code to replace names in cells

pvman · Aug 16th 2016

Hi All,

I have a rather large database, filled with certain color names in certain columns.
I am looking for a code to loop through the range where the colors names are, and replace certain color names with a different name.
Some cells have other data (can be anything) - and those cells should be left intact.
Some cells are empty and should be left empty.
Some of the colors names might have some symbols in them ("." or "/" or "&", etc.).
There are about 80 color names in my real-life database and all need to be changed/replaced with a different name.

I assumed the best way to do it is to use a "for each" loop & "select case" and I struggled with it quite a bit, without success.
I am attaching a sample data base, showing what I need.
Any help/pointers will be greatly appreciated.

TIA

**alansidman** · Aug 17th 2016

Re: Code to replace names in cells

Maybe something like this:

Code

Option Explicit




Sub Foo()
Dim c As Range
Dim rng As Range
Set rng = Application.Union(Range("A3:a7"), Range("C3:C7"), Range("e3:E7"))
For Each c In rng
If c <> "" Then
c = c & "2016"
End If
Next c




End Sub

Display More

pvman · Aug 17th 2016

Re: Code to replace names in cells

Alan, Many thanks!

Code to replace names in cells

Participate now!

Share