find and replace macro problem

[simon]

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]

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.

 

[simon]

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.












--

Dave Peterson- Hide quoted text -

- Show quoted text -

Dave thankyou very much
I will try it now