Find and Replace

[komark]

Hi,
I am trying to write a code that will find a value in certain cell in column D and try to match with values in column A. Here is an example

A B C D E
ghi abc 123
abc def 345
def ghi 678

Ok I want to change to this:

A B C D E
678 abc 123
123 def 345
345 ghi 678

Here is what I have so far but it only works if my rows matches

Sub FindnReplace()

For x = 1 To 1000 Step 1

a = Cells(x, "C").Value

If a = Range("A" & x).Value Then
b = Cells(x, "D").Value
Range("A" & x) = b

End If
Next

End Sub

 

[PHV]

A starting point:
Sub FindnReplace()
For Each a In Range("A:A")
For Each c In Range("C:C")
If a.Value = c.Value Then
a.Value = c.Offset(0, 1).Value
Exit For
End If
Next
Next
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[AnotherHiggins]

Have a look at the VLookup. You would have to use a 'helper column' rather than having the data overwrite directly, but if you're using a macro you could just copy the VLookups then paste special > Values over column A.

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[AnotherHiggins]

Oh yeah, the point is that for a lot of data using VLookup would run WAY faster than looping.

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[EBGreen]

I would use the Scripting.Dictionary object and it's .Exists method. But then I'm just a dirty scripter.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[komark]

Thank you guys for your valuable answers. I am still looking into VLookup.