Find/Replace en masse using multiple cells

**kenyacoastie** · 01-17-2012, 04:42 PM

I am trying to find code for an excel marco that will allow me to use the find/replace function on mass. I have a large excel sheet and then two control columns where one columns value needs to replace second columns value...

So...
Find G5, replace with H5 in all document;
Find G6, repalce with H6 in all document;
Find G7, repalce with H7 in all document;
...
Find G210, replace with H210 in all document;

The actual value found for the searched item (G#) needs to match completely.
I've seen tons of macros for replace a single word but couldn't find any for cells and then having them repeat.
Any ideas?

**JapanDave** · 01-17-2012, 07:08 PM

Can you post a dummy workbook with a before and after on how you want it to work? And what are you trying to match column G with?

**Lifeseeker** · 01-17-2012, 07:24 PM

Sorry guys, but JapanDave, are you able to help with another request from Lifeseeker?

**JBeaucaire** · 01-17-2012, 07:34 PM

Something like this should do it, just make sure your control sheet is active when you run it, it will execute the REPLACE function on all the other sheets.

Sub MassReplace()
Dim ws As Worksheet
Dim valRNG As Range, v As Range

With ActiveSheet
    Set valRNG = .Range("G:G").SpecialCells(xlConstants)
    
    For Each v In valRNG
        For Each ws In Worksheets
            If ws.Name <> .Name Then
                ws.Cells.Replace v.Value, v.Offset(, 1).Value, xlWhole
            End If
        Next ws
    Next v
End With

End Sub

**kenyacoastie** · 01-18-2012, 11:15 AM

Here is a sample of the data. I need to search E for any item that equals H and replace it with G.

A B D E F G H
Control # PRODUCT#........ ON WLOC. New WLOC Old WLOC
153 W88-997 2 10K1 L-06-01-A 10K1
154 W89-250 2 10K1 L-06-01-B 10K2
155 667236 2 10K10 L-06-01-C 10K3
156 667303 3 10K2 L-06-01-D 10K4
157 W89-017 3 10K2 L-06-01-E 10K5
158 W91-141 1 10K2 L-06-01-F 10K6
159 W89-726 0 10K3 L-06-01-G 10K7
160 W89-739 0 10K3 L-06-01-H 10K8
161 667286 1 10K4 L-06-01-I 10K9
162 70-42268-01 0 10K4 L-06-01-J 10K10
163 1085841 0 10K5 L-06-02-A 10L1
164 W89-527 1 10K5 L-06-02-B 10L2
165 W89-737 3 10K6 L-06-02-C 10L3
166 W89-712 1 10K7
167 W89-305 1 10K8
179 5308006930 0 10L2
180 60542001 2 10L2

**kenyacoastie** · 01-18-2012, 11:19 AM

Attachment 136989 I'm trying to post a picture of the worksheet

Location changes.jpg This is with changes for a few of the items.

Thanks for any help!!

**JBeaucaire** · 01-18-2012, 01:58 PM

So....

Sub MassReplace()
Dim ws As Worksheet
Dim valRNG As Range, v As Range

With ActiveSheet
    Set valRNG = .Range("H:H").SpecialCells(xlConstants)   'terms to search for
    
    For Each v In valRNG
        For Each ws In Worksheets
            If ws.Name <> .Name Then
                ws.Range("E:E").Replace v.Value, v.Offset(, -1).Value, xlWhole  'replaces with value 1 column to the left of the searched value
            End If
        Next ws
    Next v
End With

End Sub