Using Find & Replace in macro

  • Thread starter Thread starter Bob C
  • Start date Start date
B

Bob C

Hi Everyone

I have a speadsheet with columns of credit card
transactions.
Column B contains the name & address of the retailers as
show below:

FOODLAND PARKLAND PAR
SAMS SEAFOOD RICHMOND PAR
SHELL SELF SERVE ELIZABETH VLE
DICK SMITH W8080 ELIZABETH CIT

How do I Find and Replace all instances of PAR on the
right hand end of cell with PARK without changing
PARKLAND to PARKKLAND with two "K's" ?.

TIA

Bob C
 
Bob,

Replace PARK with a placeholder, do your replacement, and restore the
PARKs. This will work as long as XXXX doesn't appear anywhere in your
data set.

With Columns("B:B")
..Replace What:="PARK", Replacement:="XXXX", LookAt:=xlPart
..Replace What:="PAR", Replacement:="PARK", LookAt:=xlPart
..Replace What:="XXXX", Replacement:="PARK", LookAt:=xlPart
End With

HTH,
Bernie
MS Excel MVP
 
I doubt that you can. Replace doesn't offer that level of sophistication to
the best of my knowledge.

You would have to write a macro with a specific algorithm to identify this
situation and make the change.
 
Hi Tom
I have just noticed in changing my question around, I
have deleted the part about doing this via macro.
Sorry and thankyou

Bob C
 
Bob,

In case my post has been overlooked or lost:

Bob,

Replace PARK with a placeholder, do your replacement, and restore the
PARKs. This will work as long as XXXX doesn't appear anywhere in your
data set.

With Columns("B:B")
..Replace What:="PARK", Replacement:="XXXX", LookAt:=xlPart
..Replace What:="PAR", Replacement:="PARK", LookAt:=xlPart
..Replace What:="XXXX", Replacement:="PARK", LookAt:=xlPart
End With

HTH,
Bernie
MS Excel MVP
 
Hi Bernie
So that's what happens after you read "can not find ISP"
I did post a thankyou and stated your code worked just
fine, but it got lost in transit.

Thankyou
Bob C
 
I guess that wouldn't work for

EARNIE'S PARTS AND SERVICES

But if PARK and PARKK are the only conflicts, then it should work fine. I
find making such assumptions usually are not the case - thus my answer.
 
To protect any PAR?, as long as ? is a capital letter, and XXX won't
appear in the database:

Dim i As Integer

With Columns("B:B")
For i = 65 To 90
.Replace What:="PAR" & Chr(i), _
Replacement:="XXX" & Chr(i), _
LookAt:=xlPart
Next i

.Replace What:="PAR", Replacement:="PARK", LookAt:=xlPart

For i = 65 To 90
.Replace What:="XXX" & Chr(i), _
Replacement:="PAR" & Chr(i), _
LookAt:=xlPart
Next i
End With

HTH,
Bernie
MS Excel MVP
 
Hi Tom
Sorry about my reply post to you I thought you were
referring to the Ctl H Find and Replace.
The four examples are the only troublesome addresses.
But I take onboard your concerns, it's one to be very
very wary of using.

Thankyou again

Bob C
 
Hi again Bernie
Thankyou for the code. I'll try it out later on today
after I get some sleep.
It's three in the morning down under in Aussie Land.
Thankyou for your replys and time.

Bob C
 
They are one and the same. The Find used by Bernie is the VBA call to the
very same functionality called by the menu.
 
The four examples are the only troublesome addresses.

I think he is set for the 4 troublesome addresses. <g>
 
Back
Top