Find and Replace text

Hi guys,

First of all I'm not a programmer! I've been using the record macro facilty to create a macro to find and replace text. The code it generates is as follows:

Cells.Replace What:="ABCDEF", Replacement:="GHIJKL", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
False, ReplaceFormat:=False

I'm looking to run this code in column A only. However, it looks like if the search string exists in another column, the other column changes but not Column A.

How would I limit the code to a specific column or is there better coding for what I'm after?

Cheers
Roy

Originally Posted by ferrari2005

Hi guys,

First of all I'm not a programmer! I've been using the record macro facilty to create a macro to find and replace text. The code it generates is as follows:

Cells.Replace What:="ABCDEF", Replacement:="GHIJKL", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
False, ReplaceFormat:=False

I'm looking to run this code in column A only. However, it looks like if the search string exists in another column, the other column changes but not Column A.

How would I limit the code to a specific column or is there better coding for what I'm after?

Cheers
Roy

A quick run of 'record macro' (one of my favourites too) shows

    Columns("A:A").Select
    Selection.Replace What:="dot", Replacement:="comma", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

suggests you use 'selection' after selecting "A:A"

Does this help?
---

I was just doing the same thing... using the "Selection" as was previously suggested works.

I have a similar question though - is there a way to change what is being found/replaced without doing a separate Replace All for each value?

Either by having a variable or a cell location instead of what's in quotes...
Replace What:="ABCDEF", Replacement:="GHIJKL"

I tried just taking out the values in quotes and using the names of my variables, but that didn't seem to work... any ideas?

Oops, guess I forgot I had a formula in the cells I was trying to replace, now it works...

keyval = ActiveCell.Value
keycode = ActiveCell.Offset(0, 1).Value
Range("S1:S100").Select
Selection.Replace What:=keyval, Replacement:=keycode, LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False