Macro find and replace

Greg Truby

MrExcel MVP

Joined

Jun 19, 2002

Messages

10,025

• Sep 9, 2005

• #2

Where you've gone wrong is not posting your code and a clear description of your inputs; your current output; and your desired output. And welcome to the board!

 

Upvote 0

T

Tesco

New Member

Joined

Sep 9, 2005

Messages

26

• Sep 10, 2005

• #3

Find and replace

Thanks - think I've found the problem.
I need to make the find and replace only replace when it finds the complete number - it appears to be replacing part of a larger number which contains the same run off numbers - eg replace 123 it would do this on 1234567 -thus the wrong result.
Any ideas - sorry cn't uplouad files as at work.

 

Upvote 0

erik.van.geit

MrExcel MVP

Joined

Feb 1, 2003

Messages

17,832

• Sep 10, 2005

• #4

Hi, Tesco,

See xlWhole within the syntax
Selection.Replace what:="w", Replacement:="r", LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

kind regards,
Erik

 

Upvote 0

T

Tesco

New Member

Joined

Sep 9, 2005

Messages

26

• Sep 10, 2005

• #5

Hi Erik
Thanks for taking this trouble - see part below I tried putting in suggestion as you see -but still a problem - probably my ineptitude



Cells.Replace What:="145869", Replacement:="1957", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="144563", Replacement:="1517", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

 

Upvote 0

erik.van.geit

MrExcel MVP

Joined

Feb 1, 2003

Messages

17,832

• Sep 10, 2005

• #6

what's the problem ?

 

Upvote 0

T

Tesco

New Member

Joined

Sep 9, 2005

Messages

26

• Sep 10, 2005

• #7

Find and replace

THanks Erik,
Cells.Replace What:="236232", Replacement:="1891", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="145689", Replacement:="1326", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="33636", Replacement:="1089", LookAt:=xlPart, _




This is the before and after running macro




145658 Z -Gould Farrah
145869 Leonard Paul
146546 Z -Malaczynska Diana
becomes
1.10756E+11 Z -Gould Farrah
1.10755E+11 Leonard Paul
1.10757E+11 Z -Malaczynska Diana
also the last line isn't accepted - come up as syntax error

 

Upvote 0

erik.van.geit

MrExcel MVP

Joined

Feb 1, 2003

Messages

17,832

• Sep 10, 2005

• #8

last line isn't OK while ending with , _
which means you have to continue on the next line for further arguments

in fact this will do for all lines for you, I think
Cells.Replace What:="236232", Replacement:="1891", LookAt:=xlWhole

you get this scientifical notation 1.10756E+11 for large numbers
so you numbers are all in a separate column, isn't it ?

kind regars,
Erik

 

Upvote 0

T

Tesco

New Member

Joined

Sep 9, 2005

Messages

26

• Sep 10, 2005

• #9

Find and replace

That's it Erik - wonderful work - I'll make an effort now to staudy the scripting- thanks again for all your trouble - I'm a 55 year old surrounded by 20 somethings and even they couldn't figure it out.
Best wishes,

 

Upvote 0

erik.van.geit

MrExcel MVP

Joined

Feb 1, 2003

Messages

17,832

• Sep 10, 2005

• #10

a little present for the weekend
put the replacements in a sheet called list like this

Map1.xls
	A	B	C	D
1	what	replacement
2	236232	1891
3	145689	1326
4	33636	1089
list

change the sheetname "data" in the code to the name of the sheet you are working on

Code:

Option Explicit

Sub replace_list()
'Erik Van Geit
'050910
'using 2 columns

Dim LR As Long
Dim RW As Worksheet
Dim LW As Worksheet
Dim I As Integer

Set RW = Sheets("data") 'worksheet where you want to replace items
Set LW = Sheets("list") 'worksheet with replace-lists

LR = LW.Cells(65536, 1).End(xlUp).Row

For I = 2 To LR
RW.Cells.Replace what:=LW.Cells(I, 1), Replacement:=LW.Cells(I, 2), LookAt:=xlWhole
Next I

End Sub

this is more easy to use

kind regards,
Erik

 

Upvote 0