Case of replacement after Find/Replace

[BoatwrenchV8]

Hello all,

I wrote a macro to look thru a document and replace theTrade names for a medication (initial capitol letter, ex. Amoxicilline) with thegeneric names (initial lower case, ex. amoxicillin).

The problem I am having is the replacement text takes on thefound text’s case. The macro will find Amoxicilline and replace it with Amoxicillin, I want it to replace it with amoxicillin.

How do I get around this issue? :dunno Any suggestions would be appreciated.

Code posted below.

Option Explicit
Option Base 1
Sub ReplaceTradeNameWithGenericName_Ver4()

Const FilenameForTradeNameToGenericNameList As String = "Trade_Name_to_Generic_Drug_Name_List_Ver2.docx"
Const PathForForTradeNameToGenericNameList As String = "C:\Users\Rich\Documents\VBA Files Needed By Office"
Dim intCells As Integer
Dim celTable As Cell
Dim strCells() As String
Dim intCount As Integer
Dim ChangesMade As Integer
Dim rngText As Range
Dim CurrentPathNameForTheCSRtoBeCheckedAndModified As String
Dim CSRtoBeCheckedAndModified As String
Dim enumDocView As Integer
Dim docZoomLevel As Integer
'get current path and file name for CSR.
'save current path to a variable.
CurrentPathNameForTheCSRtoBeCheckedAndModified = ActiveDocument.Path & Application.PathSeparator
CSRtoBeCheckedAndModified = ActiveDocument.Name
'get view and zoom level info
enumDocView = Documents(CSRtoBeCheckedAndModified).ActiveWindow.View
docZoomLevel = Documents(CSRtoBeCheckedAndModified).ActiveWindow.View.Zoom.Percentage
'save the current file
ActiveDocument.Save
'change file open directory
ChangeFileOpenDirectory PathForForTradeNameToGenericNameList

'open trade name to generic name list. open as read only and not visible.
Documents.Open FileName:=FilenameForTradeNameToGenericNameList, ReadOnly:=True, Visible:=False, AddToRecentFiles:=False


'part 1 - read trade names and generic names into an array

' this file (Trade_Name_to_Generic_Drug_Name_List_Ver2.docx) contains a table with 2 columns of data.
' the left column contains the trade name for a medication, which starts with a capital letter
' and the right column contains the generic name for a medication which starts with a lower case
' letter.

Documents(FilenameForTradeNameToGenericNameList).Activate

If ActiveDocument.Tables.Count >= 1 Then
With ActiveDocument.Tables(1).Range
intCells = .Cells.Count
ReDim strCells(intCells)
intCount = 1
For Each celTable In .Cells
Set rngText = celTable.Range
rngText.MoveEnd Unit:=wdCharacter, Count:=-1
strCells(intCount) = rngText
intCount = intCount + 1
Next celTable
End With
End If

'close trade name to generic name list
Documents(FilenameForTradeNameToGenericNameList).Close savechanges:=wdDoNotSaveChanges
'part 2 - find trade name in CSR and replace with generic name
'Set number of changes made.
ChangesMade = 0
' activate the document to be checked, switch to normal view and change zoom
Documents(CSRtoBeCheckedAndModified).Activate
Documents(CSRtoBeCheckedAndModified).ActiveWindow.View.Type = wdNormalView
Documents(CSRtoBeCheckedAndModified).ActiveWindow.View.Zoom.Percentage = 100
Application.ScreenRefresh
'Reset the index to the array to 1
intCount = 1
While intCount < intCells
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = strCells(intCount) 'odd number = left column
.Replacement.Text = strCells(intCount + 1) 'even number = right column
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = True
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll

If Selection.Find.Found = True Then
'get total number of changes so far.
ChangesMade = ChangesMade + 1
End If

'Display a notification about what was found and replaced.
If ChangesMade = 0 Then
StatusBar = "No terms Found and Replaced yet."
ElseIf ChangesMade = 1 Then
StatusBar = "Found and Replaced 1 term so far."
Else
StatusBar = "Found and Replaced " & ChangesMade & " terms so far."
End If

'move intCount (index number for the array) down to the next row.
intCount = intCount + 2
Wend
MsgBox "Document has had all Trade names replaced with Generic names, total number of replacements = " & ChangesMade
StatusBar = ""
'switch current path and document view info back to previous values

ChangeFileOpenDirectory CurrentPathNameForTheCSRtoBeCheckedAndModified
Documents(CSRtoBeCheckedAndModified).Activate
Documents(CSRtoBeCheckedAndModified).ActiveWindow.View.Type = enumDocView
Documents(CSRtoBeCheckedAndModified).ActiveWindow.View.Zoom.Percentage = docZoomLevel
ActiveDocument.Save
Application.ScreenRefresh
End Sub

[Frosty]

Change your .MatchChase = True, and I believe that should work.

Also, there is a lot of code going on which you probably don't (or shouldn't) need...

You're opening a document with the visible property = false, and then choosing to activate it, and then working on the ActiveDocument object.

Much easier to
Dim oDoc as Document
Set oDoc = Documents.Open (blah blah arguments, including the visible = false), and then skip all the normal view/zoom level/activedocument stuff... and just refer to the oDoc.

I believe you're doing the view switching to make the processing faster, but if you keep the whole thing invisible, you don't need to perform all those operations.

Hope this helps.

[Frosty]

Also, look up "Working with Ranges" in the VBA help, and see if you can't use a range (rather than the selection).

The overall process will be much faster, and you can start to eliminate all of the code where you're saving and restoring the window "state" of the document you started in.