Hello,

I have the following problem.
In one sheet I have a table with imported keys from an
access table.
Now I want to replace these keys with strings that are in
a second worksheet. So eg when a cell in the 1st sheet
has value 2 it has to be replaced by eg "the second"...

How can I do this for all the cells of that column
automatically?

Thanks a lot.

Kristof

This is one of those cases where doing it with the macro
recorder turned on allows you to see quite easily how to
do this. Review the code, then simply edit it to make it
more applicable generally.
Here's the result of my record & adjustment:

Sub Macro1()
Dim ReplaceWhat As String
Dim ReplaceWith As String
Dim SearchCol As String

ReplaceWhat = "Test"
ReplaceWith = "NewStuff"
SearchCol = "B"


Columns(SearchCol).Replace What:=ReplaceWhat,
Replacement:=ReplaceWith, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False

End Sub

Patrick Molloy
Microsoft Excel MVP




-----Original Message-----
Hello,

I have the following problem.
In one sheet I have a table with imported keys from an
access table.
Now I want to replace these keys with strings that are
in
a second worksheet. So eg when a cell in the 1st sheet
has value 2 it has to be replaced by eg "the second"...

How can I do this for all the cells of that column
automatically?

Thanks a lot.

Kristof
.

Multiposted in misc + newusers + programming + worksheet.functions.
Aarrgghhhh!!!!