Macro for Find and Replace a Value in a Cell with Corresponding Value in Column in MS Excel 2010

[ashchow1986]

I need to write Macro for this. Column 1 has the original values and Column 2 has the values that should be replaced in Column 1.

Column1 Column2
ABC | QWE
qqq | ASH
ERTR | PRR
qqq | DIA

Find for "qqq" in each row and replace with corresponding column 2 value.

After replacement it should look like:

Column1
ABC
ASH
ERTR
DIA

I have written a simple macro for each and every row. But I need to do this using loop for the entire Sheet so that I don't keep on writing for every row individually. I'm new to this and not sure of how to write a macro for this.

Any help would be great.

 

[Richard Schollar]

Do you want to delete the column 2 values at the same time? Assuming not perhaps:

Dim cell As Range

For Each cell In Range("A1",Cells(Rows.Count,"A").End(xlUp))
  If cell.Value = "qqq" Then _
     cell.Value = cell.Offset(,1).Value
Next cell

 

[ashchow1986]

This is what I wanted. Thanks a lot
I was wondering what if the cell values to be replaced are not all in the same column.

Column1 Column2 Column 3
ABC | QWE |
qqq | ASH |
ERTR | PRR |
qqq | | DIA

Final output remains the same.
How to do this in one single loop?

 

[Peter_SSs]

If there is nothing immediately to the right of these values, try just changing the second last line of Richard's code to

cell.Value = cell.End(xlToRight).Value

 

[ashchow1986]

This is great. Thanks.