Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Replace text with variable using VBA replace code? | Excel Programming | |||
find and replace - replace data in rows to separated by commas | Excel Worksheet Functions | |||
How can I use replace(alt+H) for mutiple items needing replace | Excel Worksheet Functions | |||
Excel 2000/XP script to Excel97 script | Excel Programming | |||
VB Script To replace space with nothing | Excel Programming |