• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Replace using Right function

J

Jakethedog422

New Member
I have created a macro to convert inconsistent addresses to a standard USPS convention. In the macro I have a column selected and I replace several words and abbreviations to standard abbreviations. Im having trouble replacing "So" when it appears as the last 2 letters of any cell in the selected column.

Code: 
Selection.Replace What:=Right("So", 2), Replacement:="S", LookAt:=xlPart, _
     SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
     ReplaceFormat:=False
When I run this right now, it will change "So" anywhere in the string where the case matches.

I am a VBA newb. I am completely self taught and this is my first post, so please be kind and keep it as simple as possible. I dont know all the speak.

Any help is appreciated.
 
Hi,
Code: 
LookAt:=xlPart
is kind of a give away. When "So" matches part of a string it will get replaced. Perhaps you want to replace " So " (extra spaces)? Without seeing some representative data, it is difficult to say.
 
In this case, I am looking to catch So when it is used as the abbreviation for South and it appears as the last 2 characters in any cell in a selected range. Example: If the cell shows 123 Ea 456 So I have the code to replace the "Ea" (using the spaces as you suggested), however the same does not work for the So as it is at the end of the string. Looking for "[space]So" causes the same issue as just looking for "So".

Is there some change I can make to LookAt:=xlpart? Could xlpart be changed to xlexpression, Formula and then show the RIGHT("So",2) as the formula. Do you have a suggestion as to what that would look like?

Or, I also considered doing this as an "If" statement but I think that would have to start in the first cell of the selection and then have a "next" in the code too. Is that a better approach? Could you give me a suggestion of what the "If" statement would look like?
 
I can't help any further as my vba skills are not that advanced as many other on this forum. You'd have more luck getting the correct answer if you upload a small, yet representative sample of data and your code.

The if/next might work, but it all depends on the number records to treat and how it is constructed. I've seen in other cases that VBA ninjas were able to reduce running time of code from minutes to below 1 s just by coding smarter.
 
You must log in or register to reply here.
Back
Top