Need Sheets.Cells.Replace Guidance

[blairacuda]

hello all,

i have a function that replaces abbreviations in a sheet with the actual word(s) associated with the applicable abbreviation. the problem i'm running in to is which properties of the replace function to set.

code:
Sheets(shtName).Cells.Replace what:=abbreviation, replacement:=replacement, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False

my problem is this scenario: PIP vs PIPP. with LookAt set to xlPart it only reads a portion of the string and sees PIPP as matching PIP. if i use xlWhole for LookAt it looks at the whole string (and the abbreviation can be anywhere in the string picked up from a cell). is there a setting i can use to tell it to respect each individual "word" as separated by whitespace to find the abbreviation to replace?

thanks in advance.

CBlair
Crystal, InstallShield, branching out in other programming realms.

 

[SkipVought]

hi,

abbreviation = " PIP "

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[N1GHTEYES]

Skip's method works, but it would miss cases where your abbreviation was not surrounded by space, for example, "(PIP)" or " PIP," or " PIP.".

What you could do is:

Check each of your abbreviations to see if they contain any other abbreviations and create a list of those that do.

For each item in turn on the above list, search and replace your whole text for that item (e.g. PIPP), replacing it with some unlikely placeholder, say "^&%$%^%" or whatever you choose. Then search and replace for the smaller abbreviation, (e.g. PIP). Then you can replace "^&%$%^%" with your full version of the abbreviation it replaced (e.g. PIPP).

Do that for each abbreviation which contains other abbreviations.

Then replace the rest of the abbreviations using the normal process.

Does that make sense?

Tony

 

[PWD]

The post says,

each individual "word" as separated by whitespace

so Skip's solution looks good to me!

Tony's post made me wonder if there would be any mileage, under slightly different circumstances, of considering using

InStr

in conjunction with a 'Find' to determine whether or not to execute the 'Replace'? Would that be too clunky? 1) Find "PIP" & obtain address; 2) Check address for 'Instr' "PIPP"; If 'Instr' is false, do the replace. You would still need to list (or know) which abbreviations are contained in other words.

Anyway, just my two-penneth

Des.

Many thanks,
D€$

 

[blairacuda]

sorry for the late reply. family is over for the holidays and all.

pwd's suggestion is the kind of thing I'm going to go with. pick up the string from the cell and handle it manually.

I did forget to mention the abbreviations are stored in an external file. so the current workaround is to order them longest string to shortest and that works for now. but, again, I'm going to have to handle the replacement manually to get what I want.

I'm quite surprised vba doesn't have an option with their built replace function to help with this.

CBlair
Crystal, InstallShield, branching out in other programming realms.

 

[N1GHTEYES]

If your abbreviations really are separated by whitespace, I don't see why Skip's method would not work. If they are not, then my method should. You could use the Replace method with both.

VBA also has other related string functions and subs (rather than methods), such as Mid, Substitute, Find, Exact, Replace and Search. Have a look at those in the help.

I'm pretty sure that if you are clear in your mind about exactly what it is you are trying to do, you should be able to automate this.

Tony