Find and Replace is going wrong in some cases

P

pol

Selection.Replace What:="4", Replacement:="C.LIABLITIES", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False

When I am giving the above condion at the end of the following replace
statement , the result is showing wrongly. Please anybody can suggest a good
openion for this.


Columns("S:S").Select
Range("S1").Activate

Selection.Replace What:="0", Replacement:="ASSET", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Columns("S:S").AutoFit



Selection.Replace What:="1", Replacement:="C.ASSET", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False


Selection.Replace What:="2", Replacement:="F.ASSET", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False



Selection.Replace What:="3", Replacement:="LIABLITIES", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False

With Regards
Pol
 
G

Gary''s Student

If column S contains:

1
3
2
1
4
0
1
3
2
0
2
4
2
1
3
1
4
1
0
4
1
0
2
0
0
4
3
4
2
3
4
1
1
4
2
1
1
1
3
1

then running:

Sub serviant()

Dim r As Range
Set r = Range("S:S")

r.Replace What:="0", Replacement:="ASSET", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Columns("S:S").AutoFit



r.Replace What:="1", Replacement:="C.ASSET", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False


r.Replace What:="2", Replacement:="F.ASSET", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False



r.Replace What:="3", Replacement:="LIABLITIES", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False


r.Replace What:="4", Replacement:="C.LIABLITIES", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False

End Sub

will produce:

C.ASSET
LIABLITIES
F.ASSET
C.ASSET
C.LIABLITIES
ASSET
C.ASSET
LIABLITIES
F.ASSET
ASSET
F.ASSET
C.LIABLITIES
F.ASSET
C.ASSET
LIABLITIES
C.ASSET
C.LIABLITIES
C.ASSET
ASSET
C.LIABLITIES
C.ASSET
ASSET
F.ASSET
ASSET
ASSET
C.LIABLITIES
LIABLITIES
C.LIABLITIES
F.ASSET
LIABLITIES
C.LIABLITIES
C.ASSET
C.ASSET
C.LIABLITIES
F.ASSET
C.ASSET
C.ASSET
C.ASSET
LIABLITIES
C.ASSET
 
P

pol

Still it is showing wrongly . Please can you try for me with the following
example.
When I try the following example ,It is come up with wrong result .

Dim r As Range
Set r = Range("S:S")
'Summary code description
'Columns("S:S").Select
'Range("S1").Activate

r.Replace What:="0", Replacement:="UNALLOCATED ITEMS", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Columns("S:S").AutoFit



r.Replace What:="1", Replacement:="NEW CAR, VAN & 4x4.", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False


r.Replace What:="2", Replacement:="NEW TRUCK TYRES", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False



r.Replace What:="3", Replacement:="AGRI TYRES", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False

r.Replace What:="4", Replacement:="pol", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False




r.Replace What:="5", Replacement:="A.T.V. / LAWN & TURF",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False



r.Replace What:="6", Replacement:="MOTORCYCLE", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False

r.Replace What:="7", Replacement:="PART WORN TYRES", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

r.Replace What:="8", Replacement:="REPAIRS & FITTING (CONSUMER)",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

r.Replace What:="9", Replacement:="VALVE & BALANCE (CONSUMER)",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False


r.Replace What:="A", Replacement:="REBILLING", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

r.Replace What:="B", Replacement:="BATTERIES", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

r.Replace What:="C", Replacement:="BREAKDOWNS", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

r.Replace What:="D", Replacement:="DELIVERY CHARGES", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

r.Replace What:="E", Replacement:="RETREAD TRUCK TYRES", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

r.Replace What:="F", Replacement:="CONSUMABLES", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

r.Replace What:="G", Replacement:="DISPOSAL CAR, VAN & 4X4",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

r.Replace What:="H", Replacement:="LABOUR CHARGE (GARAGE)",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

r.Replace What:="I", Replacement:="WHEEL ALIGNMENTS", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

r.Replace What:="J", Replacement:="REPAIRS & FITTING (TRUCK)",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

r.Replace What:="K", Replacement:="TRUCK CASINGS CHARGES",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

r.Replace What:="L", Replacement:="DISPOSAL OTHER", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

r.Replace What:="M", Replacement:="CUSTOMER RETAINED USED GOODS",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

r.Replace What:="N", Replacement:="WAITING ROOM ACCESSORIES",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

r.Replace What:="O", Replacement:="LABOUR CHARGE (BREAKDOWNS)",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

r.Replace What:="P", Replacement:="DISPOSAL TRUCK", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

r.Replace What:="Q", Replacement:="REPAIRS & FITTING (OTHER)",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

r.Replace What:="R", Replacement:="RETREAD CAR, VAN & 4X4",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

r.Replace What:="S", Replacement:="SERVICING (PARTS)", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

r.Replace What:="T", Replacement:="TUBES/FLAPS", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

r.Replace What:="U", Replacement:="COMMERCIAL & AGRI WHEELS",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

r.Replace What:="V", Replacement:="VALVE & BALANCE (TRUCK)",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

r.Replace What:="W", Replacement:="ALLOY WHEELS", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

r.Replace What:="X", Replacement:="EXHAUST PARTS", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

r.Replace What:="Y", Replacement:="EXPENSE ITEMS", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

r.Replace What:="Z", Replacement:="MISC. NON STOCK", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
 
G

Gary''s Student

I tried you code and it works. The problem must be in the data.

I formatted column S as Text and inserted only a single character in each
cell.
 
J

Jacob Skaria

Dear

Change LookAt:=xlPart to

LookAt:=xlWhole (if that suits your requirement) ....because xlPart is
replacing characters/numerics which are already replaced...

For example
"1" is replaced with "NEW CAR, VAN & 4x4.", LookAt:=xlPart
Then
"4" is replaced with "pol"...which will replace the 4's in "NEW CAR, VAN &
4x4."

and like wise many more......

If this post helps click Yes
 
P

pol

Thanks for your greateopenion . Please can you advice a good openion to
solve this problme from ur knowledge. I have one openion to give ie, if that
column have only one charector , then should replace other wise donot replace.

Please can you help
regards
Pol
 
P

pol

Otherwise how I can give that replace condition in a loop

for example

for range.select .firstrow which match the condition
replace
nextrow which match the condition
replace
Next.

But my fear it will make slow in the worksheet . Please advice..
 
J

Jacob Skaria

1. From code window try replacing all xlPart with xlWhole..This should work

Better way to handle this is to enter the data into a new sheet say 'Index'
Col A with codes and Col B with the replace text..and use the below
code...(untested ). You should be able to modify this to suit your
requirments...


Columns("S:S").Select
lnglastRow = <enter number of replacements>
For lngRow = 1 to lngLastRow
strFind = Sheets("Index").Range("A" & lngRow)
strReplace = Sheets("Index").Range("B" & lngRow)
Selection.Replace What:=strFind, Replacement:=strReplace, LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Next
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

to read text tablimited file 2
Error 400 when running Macro 3
find and replace multiple values in one statement 3
worksheet change question 2
Macro not working properly when run automatically 9
Replaceformat problem 2
Macro that isn't working 2
Problem with Macro 7

Top