Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
find and replace macro problem
Hi everyboby
I'm new to macro programing and am trying to write a simple, or so I thought, replace macro in excel 2007. I have two sheets on the same workbook one called "colour full" and one called "colour" Sheet "colour" contains a table with colour code 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 in how to input the active cell value in to the "Replace What:=" function and the active cell offset value in to the "Replacement:=" function. 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 ' ActiveCell.Select Sheets("colour full").Select ActiveCell.Columns("A:A").EntireColumn.Select Selection.Replace What:=cell.Value, Replacement:=cell.offset(0,-3)."Value", LookAt :=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Sheets("colour").Select ActiveCell.Offset(1, 0).Range("A1").Select End Sub Thanks Simon |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
find and replace macro problem
I don't think that you can do this kind of thing safely with a single loop.
If you wanted red to change to Green and green to change to blue, Then the first replace would change the first red to green. But the second replace would change that new green to blue (along with the original green). I'd use two edit|replaces. The first one to change each color to a unique string that isn't used anywhere else. The second that changes those unique strings to the colors that you want. I put the list that contained the specifications in "Colour Full" in A1:B##. (I'm gonna loop through column A and use .offset(0,1) to get the value in column B). Option Explicit Sub testme() Dim ListWks As Worksheet Dim ColWks As Worksheet Dim myList As Range Dim myCell As Range Set ListWks = Worksheets("Colour Full") Set ColWks = Worksheets("colour") With ListWks Set myList = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With With ColWks.Range("a:a") For Each myCell In myList.Cells .Cells.Replace What:=myCell.Value, _ Replacement:="XXXXX" & Format(myCell.Row, "000000"), _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ MatchCase:=False, _ SearchFormat:=False, _ ReplaceFormat:=False Next myCell For Each myCell In myList.Cells .Cells.Replace What:="XXXXX" & Format(myCell.Row, "000000"), _ Replacement:=myCell.Offset(0, 1).Value, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ MatchCase:=False, _ SearchFormat:=False, _ ReplaceFormat:=False Next myCell End With End Sub This assumes that you don't have any strings like XXXXX000001, XXXXX000002, ... in your data. If you do, then use a different prefix. wrote: Hi everyboby I'm new to macro programing and am trying to write a simple, or so I thought, replace macro in excel 2007. I have two sheets on the same workbook one called "colour full" and one called "colour" Sheet "colour" contains a table with colour code 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 in how to input the active cell value in to the "Replace What:=" function and the active cell offset value in to the "Replacement:=" function. 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 ' ActiveCell.Select Sheets("colour full").Select ActiveCell.Columns("A:A").EntireColumn.Select Selection.Replace What:=cell.Value, Replacement:=cell.offset(0,-3)."Value", LookAt :=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Sheets("colour").Select ActiveCell.Offset(1, 0).Range("A1").Select End Sub Thanks Simon -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
find and replace macro problem
On Mar 30, 5:41 pm, Dave Peterson wrote:
I don't think that you can do this kind of thing safely with a single loop. If you wanted red to change to Green and green to change to blue, Then the first replace would change the first red to green. But the second replace would change that new green to blue (along with the original green). I'd use two edit|replaces. The first one to change each color to a unique string that isn't used anywhere else. The second that changes those unique strings to the colors that you want. I put the list that contained the specifications in "Colour Full" in A1:B##. (I'm gonna loop through column A and use .offset(0,1) to get the value in column B). Option Explicit Sub testme() Dim ListWks As Worksheet Dim ColWks As Worksheet Dim myList As Range Dim myCell As Range Set ListWks = Worksheets("Colour Full") Set ColWks = Worksheets("colour") With ListWks Set myList = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With With ColWks.Range("a:a") For Each myCell In myList.Cells .Cells.Replace What:=myCell.Value, _ Replacement:="XXXXX" & Format(myCell.Row, "000000"), _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ MatchCase:=False, _ SearchFormat:=False, _ ReplaceFormat:=False Next myCell For Each myCell In myList.Cells .Cells.Replace What:="XXXXX" & Format(myCell.Row, "000000"), _ Replacement:=myCell.Offset(0, 1).Value, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ MatchCase:=False, _ SearchFormat:=False, _ ReplaceFormat:=False Next myCell End With End Sub This assumes that you don't have any strings like XXXXX000001, XXXXX000002, ... in your data. If you do, then use a different prefix. wrote: Hi everyboby I'm new to macro programing and am trying to write a simple, or so I thought, replace macro in excel 2007. I have two sheets on the same workbook one called "colour full" and one called "colour" Sheet "colour" contains a table with colour code 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 in how to input the active cell value in to the "Replace What:=" function and the active cell offset value in to the "Replacement:=" function. 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 ' ActiveCell.Select Sheets("colour full").Select ActiveCell.Columns("A:A").EntireColumn.Select Selection.Replace What:=cell.Value, Replacement:=cell.offset(0,-3)."Value", LookAt :=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Sheets("colour").Select ActiveCell.Offset(1, 0).Range("A1").Select End Sub Thanks Simon -- Dave Peterson- Hide quoted text - - Show quoted text - Dave thankyou very much I will try it now |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find/Replace Problem | Excel Discussion (Misc queries) | |||
Using Find and Replace to replace " in a macro | Excel Programming | |||
Find-Replace problem | Excel Discussion (Misc queries) | |||
Find replace problem | Excel Discussion (Misc queries) | |||
Problem with Find and Replace | Excel Worksheet Functions |