is it possible to Restrict Find & Replace to 1 column

[Simon]

I need to Find and Replace values in a Column

Same data also exists in other columns - so I would like to restrict the
Find and Replace to
a specified column

I need to do this in a Macro ie. popup a text box

 

[JE McGimpsey]

Just select the column before doing the F&R.

 

[Simon]

No, that dosen't work

Also replaces values that exist in other columns !

This is the Macro I recorder

Sub Macro2()
Columns("W:W").Select
Selection.Replace What:="740", Replacement:="989", LookAt:=xlWhole, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

Other columns with 740 in it are also replaced with 989

 

[JE McGimpsey]

Does for me. XL04, XL03.

Your macro works fine as well. Only replaces 740 in column W.


What happens when you don't select:

Sub Macro3()
Columns("W:W").Replace _
What:=740, _
Replacement:=989, _
LookAt:=xlWhole
End Sub

 

[Simon]

I'm using Office XP Pro - XL2002 SP2

I'll see if we have a machine 2003 on it!

 

[Simon]

Thanks

Works ok with 2002 SP3

Does for me. XL04, XL03.

Your macro works fine as well. Only replaces 740 in column W.


What happens when you don't select:

Sub Macro3()
Columns("W:W").Replace _
What:=740, _
Replacement:=989, _
LookAt:=xlWhole
End Sub