«

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

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)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------



"John" <> wrote in message
news:...
> 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
>
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.562 / Virus Database: 354 - Release Date: 16/01/2004

On Mon, 19 Jan 2004 21:44:59 -0000, "Ken Wright"
<> wrote:

>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

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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------



"John" <> wrote in message
news:...
> On Mon, 19 Jan 2004 21:44:59 -0000, "Ken Wright"
> <> wrote:
>
> >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
>
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.562 / Virus Database: 354 - Release Date: 16/01/2004