Find Replace with Macro

J

jb7395

Can anyone tell me how to construct a macro that will seacrh the entire
contents of column C, find the contacts of cell A1 and replace it with B1,
then do the same finding A2 and replacing it with B2 in column C, then A3
with B3 in column C etc etc. I can not get this to work.
 
K

kkknie

Sub Test()

Dim r1 as Range
Dim r2 as Range

For Each r1 in Range("A1:A" & Range("A65536").End(xlUp).Row
For Each r2 in Range("C1:C" & Range("C65536").End(xlUp).Row
If r1.Value = r2.Value Then r2.Value = Range("B" & r1.Row).Value
Next
Next

End Sub
 
J

jb7395

Thank you kkkknie but I got a compile message on the text supplied, so I
added a close parenthesis after the x1up portion of the For each so that it
now reads:

For Each r1 In Range("A1:A" & Range("A65536").End(xlUp)).Row
For Each r2 In Range("C1:C" & Range("C65536").End(xlUp)).Row

However I still get a compile error which tells me that "For Each may only
iterate over a collection object or an array.

What do I need to do??
 
I

icestationzbra

just a little variation from the original one, try it:

Sub test()

Dim r1 As Range
Dim r2 As Range

For Each r1 In Range("A1:A" & Range("A65536").End(xlUp).Row)
For Each r2 In Range("C1:C" & Range("C65536").End(xlUp).Row)
If r1.Value = r2.Value Then r2.Value = Range("B" & r1.Row).Value
Next r2
Next r1

End Su
 
K

kkknie

Sorry about that. Not sure where the last parenthesis went to since
tested it then copied/pasted. Who knows... The error is that yo
re-added the last parenthesis in the wrong place.

For Each r1 In Range("A1:A" & Range("A65536").End(xlUp).Row)
For Each r2 In Range("C1:C" & Range("C65536").End(xlUp).Row)

What you are doing here is creating a range starting at A1 and going t
the last value in the A column. The last row is found using thi
expression Range("A65536").End(xlUp).Row

When you put it all together, you get Range("A1:A100") if 100 is you
last row...
 
J

jb7395

Great. This works except it only updates the first record in column C.
However I've found if I force the C range as follows:

For Each r2 In Range("C1:C1000" & Range("C65536").End(xlUp).Row)
then
For Each r2 In Range("C1000:C2000" & Range("C65536").End(xlUp).Row)


It updates 10,000 records at a time. Perhaps it's a memory issue???

Either way updating 10,000 records at a time is exremely helpful. Thank you.
 
D

Dave Peterson

How about this:

Option Explicit
Sub testme()

Dim myArray As Variant
Dim iCtr As Long

With ActiveSheet
myArray = .Range("a1:B" & .Cells(.Rows.Count, "A").End(xlUp).Row).Value
With .Range("c:c")
For iCtr = LBound(myArray, 1) To UBound(myArray, 1)
.Cells.Replace what:=myArray(iCtr, 1), _
replacement:=myArray(iCtr, 2), _
lookat:=xlPart, MatchCase:=False
Next iCtr
End With
End With
End Sub

I guessed at the parms you wanted to use in the .replace (xlpart/xlwhole,
matchcase).
 
Top