Macro To Replace things?

[John]

Hello.

I have a macro which replaces certain numbers entered in column 1 of a
spreadsheet with different numbers.

I have a slight error in my macro though. This is an example of the
macro. It changes 123 to 1271, 234 to 5501 etc.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 10/12/2003 by John
'
' Keyboard Shortcut: Ctrl+k
'
Cells.Replace What:="123", Replacement:="1271", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="234", Replacement:="5501", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="23", Replacement:="3006", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="12", Replacement:="4000", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End Sub

Unfortunately the problem is that it doesn't look for whole numbers.
Instead of only just replacing the number 12 with 4000, what it will
do is replace any number containing 12 to that as well. For example
if I have 701249, it will replace the 12 in that number so I'll get
70400049.

Is there a way I can avoid this and make it so that it will only
replace the whole number if found and not parts of numbers?

Thanks for your help

John

 

[Ken Wright]

The answer is staring you in the face in each line of your code. Hint:-

LookAt:=xlPart (Take a guess at what it should be, and I mean a 'whole'
guess, not just 'part' of a guess)

 

[John]

The answer is staring you in the face in each line of your code. Hint:-

LookAt:=xlPart (Take a guess at what it should be, and I mean a 'whole'
guess, not just 'part' of a guess)

Thanks for your help.

I've not done too much work with macros before but I'm an idiot for
not spotting that

John

 

[Ken Wright]

LOL - Shouldn't worry - We've all been there.