Replace script
 

Hi everyboby
I'm having a problem with a short replace macro i'm trying to write in
excel

I have two sheets on the same workbook one called "colour full" and
one called "colour"
Sheet "colour" contains a table with colour code (range name
"ColourID") and the coresponding
description. Sheet "colour full" contains a column of colour code
which i would like to replace with the description.

my problem seems to be the
For Each Cell In Worksheets("colour").Range("ColourID")
line as the script only replaces the first row on the sheet "colour",
not then moving down to the next row and repeating the replace
function untill it has run to the end of the "ColourID" range.

No doubt it is a stupid/obvious error I have made but I'm new to this
and any help would be much apreciated as i have a lot of these replace
tasks to do.


Sub colour()
'
' colour Macro
'
' Keyboard Shortcut: Ctrl+l
'
Worksheets("colour full").Activate
Worksheets("colour full").Range("A1").Activate
ActiveCell.Columns("A:A").EntireColumn.Select
For Each Cell In Worksheets("colour").Range("ColourID")
Cells.Replace What:=Cell.Value, Replacement:=Cell.Offset(0,
1).Value, _
LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False
Next

End Sub

Many thanks
Simon

Replace script
 

Hi, in my opinion, the range (ColourID) seems to dictate the cells being
affected. Try increasing the range.

" wrote:

Hi everyboby
I'm having a problem with a short replace macro i'm trying to write in
excel

I have two sheets on the same workbook one called "colour full" and
one called "colour"
Sheet "colour" contains a table with colour code (range name
"ColourID") and the coresponding
description. Sheet "colour full" contains a column of colour code
which i would like to replace with the description.

my problem seems to be the
For Each Cell In Worksheets("colour").Range("ColourID")
line as the script only replaces the first row on the sheet "colour",
not then moving down to the next row and repeating the replace
function untill it has run to the end of the "ColourID" range.

No doubt it is a stupid/obvious error I have made but I'm new to this
and any help would be much apreciated as i have a lot of these replace
tasks to do.


Sub colour()
'
' colour Macro
'
' Keyboard Shortcut: Ctrl+l
'
Worksheets("colour full").Activate
Worksheets("colour full").Range("A1").Activate
ActiveCell.Columns("A:A").EntireColumn.Select
For Each Cell In Worksheets("colour").Range("ColourID")
Cells.Replace What:=Cell.Value, Replacement:=Cell.Offset(0,
1).Value, _
LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False
Next

End Sub

Many thanks
Simon