I have a main spreadsheet that contains approximately 10,000 names and addresses..... There are about 20 columns..... I am using the code below to replace abbreviations with whole words...... The abbreviations are in several of the columns in Sheet1, which is the main list.....
Sheet2 is the substitution list. It is in the following form....
There are about 400 items on Sheet2. (The substitution list)
This code does a very good job of replacing the abbreviation with the whole word..... Problem is, it replaces the abbreviation if it is part of another word! Example: "South American Airlines" is changed to "South Americaican Airlines". It finds "Amer" embedded inside the word American. I want it to be able to find the abbreviation anywhere in the cell, but not inside another word. If it is at the first of the cell, then it would not be preceeded with a space, but could have a trailing space. If at the end of the cell, then it could be preceeded with a space, but would not have a trailing space. These 2 situations could indicate that the abbreviation is imbedded as the first part or last part of another word......... Also if the abbrevation is the ONLY word in the cell then it should be substituted...... However if it is embedded in another word, then it should be ignored...... Of course we should search to see if the abbreviation is the 1st, 2nd, 3rd etc... part of the cell.....
I am using the inputbox to select which columns the sub is applied to............
Any help on this will be much Appreciated...............
Dim rColumn As Range, vAbbr As Variant
Dim i As Long
On Error Resume Next
Set rColumn = Application.InputBox(Prompt:="Please select a range with your Mouse to fix Abbreviations.", Title:="SPECIFY RANGE", Type:=8)
On Error GoTo 0
If Not rColumn Is Nothing Then
Set rColumn = Application.Intersect(ActiveSheet.UsedRange, rColumn.EntireColumn)
vAbbr = .Range("A2:B" & .Cells(.Rows.Count, "A").End(xlUp).Row).Value
For i = 1 To UBound(vAbbr, 1)
rColumn.Replace vAbbr(i, 1), vAbbr(i, 2), Lookat:=xlPart, MatchCase:=False
MsgBox "No range selected! Exiting Sub...": Exit Sub