macro to find and replace the case of different characters in a two-column table

Anonymous
2016-06-24T10:32:23+00:00

Hi team,

Currently I am working with this macro

Sub ReplaceFromTableList()

Dim oChanges As Document, oDoc As Document

Dim oTable As Table

Dim oRng As Range

Dim rFindText As Range, rReplacement As Range

Dim i As Long

Dim sFname As String

Dim sAsk As String

    sFname = "C:\Users\Win7\Desktop\macro.docx" 'The table document

    Set oDoc = ActiveDocument

    Set oChanges = Documents.Open(FileName:=sFname, Visible:=False)

    Set oTable = oChanges.Tables(1)

    For i = 1 To oTable.Rows.Count

        Set oRng = oDoc.Range

        Set rFindText = oTable.Cell(i, 1).Range

        rFindText.End = rFindText.End - 1

        Set rReplacement = oTable.Cell(i, 2).Range

        rReplacement.End = rReplacement.End - 1

        With oRng.Find

        .ClearFormatting

        .Replacement.ClearFormatting

        .Execute FindText:=rFindText.Text, _

                 MatchWildcards:=True, _

                 ReplaceWith:=rReplacement.Text, _

                 Replace:=wdReplaceAll

    End With           

    Next i

    oChanges.Close wdDoNotSaveChanges

lbl_Exit:

    Exit Sub

End Sub

This macro finds terms in the first column of a table and replaces them by those on the second column, and it enables you to use wildcards as follows

([Hh])owever \1wv.
<([Aa])s well as> \1wa.

Yet, in some pairs the first character of the first column is not the same as the first one of the second column, as in

expression xprss.

Therefore, I have two questions, namely

a) whether it is possible to use wildcards to somehow carry out an operation which would transmit the upper/lower case of the first character of the first column into the first character of the second column, so that both coincide

b) whether, if such an operation is not possible using wildcards, the macro I am working with (or even a completely different one) could be modified so that my purpose can be succesfully achieved.

I'd very much appreciate any ideas on the alternatives I have.

Microsoft 365 and Office | Word | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
Doug Robbins - MVP - Office Apps and Services 323.6K Reputation points MVP Volunteer Moderator
2016-07-03T00:54:16+00:00

In that case, use:

With Sheets(1).Range("A1")

    For i = 0 To .CurrentRegion.Rows.Count

        For k = 0 To 1

            For j = 1 To .Offset(i, k).Characters.Count

                If IsNumeric(Left(.Offset(i, k), 1)) Then

                    GoTo Nextj

                End If

                If Asc(Left(.Offset(i, k), 1)) > 64 And Asc(Left(.Offset(i, k), 1)) < 90 Then

                    GoTo Nextj

                End If

                If Asc(Mid(.Offset(i, k), j, 1)) > 96 And Asc(Mid(.Offset(i, k), j, 1)) < 123 Then

                    .Offset(i, k) = Left(.Offset(i, k), j - 1) & UCase(Mid(.Offset(i, k), j, 1)) & Mid(.Offset(i, k), j + 1)

                    Exit For

                End If

Nextj:

            Next j

        Next k

     Next i

End With

Was this answer helpful?

0 comments
Answer accepted by question author
Paul Edstein 82,871 Reputation points Volunteer Moderator
2016-06-26T02:05:53+00:00

I've made some minor revisions to the previous code, so both macros should now run without error.

Regarding the formatting, unless you use a 4-column table, there's really no reliable way of knowing whether the formats found in the cells are important to the Find/Replace operation. How would one tell, for example, whether the absence of a bold font means the content to be found is not to be bolded, or that bolding is inconsequential? Separate columns to designate the applicability of font attributes for both the Find and the Replace would be required. the following macro envisages such a table, where the presence of content in the 3rd & 4th columns dictate whether the font format is to be taken into account for the Find (column 3) or applied to the Replace (column 4); if the cell has content, the formatting there is applied to the Find and/or Replace, as applicable.

Sub ReplaceFromTableList()

Application.ScreenUpdating = False

Dim Doc As Document, Rng As Range, i As Long, Tbl As Table

Dim sFname As String, StrFnd As String, StrRep As String

sFname = "C:\Users\Win7\Desktop\macro.docx" 'The table document

Set Rng = ActiveDocument.Range

Set Doc = Documents.Open(FileName:=sFname, Visible:=False)

Set Tbl = Doc.Tables(1)

With Rng.Find

  .MatchWildcards = True

  .Wrap = wdFindContinue

  For i = 1 To Tbl.Rows.Count

    .ClearFormatting

    .Replacement.ClearFormatting

    StrFnd = Split(Tbl.Cell(i, 1).Range.Text, vbCr)(0)

    StrRep = Split(Tbl.Cell(i, 2).Range.Text, vbCr)(0)

    .Text = StrFnd

    .Replacement.Text = StrRep

    If Split(Tbl.Cell(i, 3).Range.Text, vbCr)(0) <> "" Then

      Set FntFnd = Tbl.Cell(i, 4).Range.Characters(1).Font

      .Font.Bold = FntFnd.Bold

      .Font.Italic = FntFnd.Italic

      .Font.Underline = FntFnd.Underline

    End If

    If Split(Tbl.Cell(i, 4).Range.Text, vbCr)(0) <> "" Then

      Set FntRep = Tbl.Cell(i, 4).Range.Characters(1).Font

      With .Replacement

        .Font.Bold = FntRep.Bold

        .Font.Italic = FntRep.Italic

        .Font.Underline = FntRep.Underline

      End With

    End If

    .Execute Replace:=wdReplaceAll

    .Text = LCase(StrFnd)

    .Replacement.Text = LCase(StrRep)

    .Execute Replace:=wdReplaceAll

  Next i

End With

Doc.Close wdDoNotSaveChanges

Application.ScreenUpdating = True

End Sub

Was this answer helpful?

0 comments

46 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2016-06-26T18:48:37+00:00

    Hi Paul,

    Thak you so much for your last post, because at last I understood it and it really works.

    Nonetheless, there are some issues I still need to solve so that, foreseeing future uses, I can use the macro in a fully functional fashion. I'll try to be as concise as possible.

    I assume that a general description of the macro would be on the following lines:

    "Except for one feature of formatting (I have chosen "upper/lower case") of one particular character (I have chosen the "first character" in both the first and second cells), any other formatting can be applied to any character, including the first one, both in the Find (third cell) and in the Replace (fourth cell) operations".

    Therefore, the fundamental parameters that I'd like to control are:

    1. Upper/lower case is a binary option in itself, meaning that having one case necessarily implies not having the other and vice versa —I wonder whether this has to do with the fact that the process is most efficient using LCase conversion.

    That said, I'm ignorant of whether any other formatting that by nature is non-binary could also be used for this purpose, presumably in a binary sense of

    a) the feature appears

    b) the feature does not appear (for example, color, style, size, etc., for which the lack of a certain property of formatting does not imply necessarily that another specific option is present : for example, a character which is not red-highlighted does not have to be highlighted in another specific color; in fact, it may not be highlighted at all).

    1. The correlation of the first and the second columns could be established between characters which occupied different numeric positions in their respective cells.
    2. Currently, in the third/forth cells, the code only allows three different formatting (i.e, .Font.Bold, .Font.Italic, .Font.Underline), yet  the optimal would be for the code to allow any fomatting, such as Typeface (Calibri, Arial, etc.), Theme colors, Size, Font effects (Small caps, Strikethrough, etc.), Styles, etc.

    And even the feature of formatting chosen for the first and second cells might be allowed (regarding this last option, either it's allowed for the rest of the characters, but not for the ones chosen in the first/second cells, or alternatively it is allowed even for those chosen characters so that, in case of contradiction, it would be overwritten by the formatting appearing in the third/forth cells, which the code seems to indicate are 'processed' after the first two cells —yet, I guess that this overwriting could affect the efficiency of the macro).

    1. The first character of the third/fourth cells spreads it's formatting to the rest of the characters regardless of the formatting of these other characters. I'd like to know whether it would be possible to apply different formatting to any other characters of the third/fourth cells without implying its spreading to the rest, such as
    Transitive Trive. Transitive Trive.

    Was this answer helpful?

    0 comments
  2. Paul Edstein 82,871 Reputation points Volunteer Moderator
    2016-06-26T13:59:36+00:00

    Nothing you change for a second character in columns 3 and 4 has any effect on the Find/Replace. Only the first character in those columns is taken into account.

    At its simplest, consider:

    Transitive Tr.
    Transitive Tr. X ****
    Transitive Tr. X
    Transitive Tr. X X

     With Row:

    1. Because columns 3 & 4 are empty, no font attributes are taken into account for either the Find or the Replace.
    2. Because column 3 is not empty and its first character has a bold font, whatever is found must also be bold, but not underlined or italics. The replaced content is left as it was found - bold.
    3. Because column 3 is empty, no font attributes are taken into account for the Find. Because column 4 is not empty and its first character has an italic font, whatever is found will be replaced with italics.
    4. Because columns 3 & 4 are not empty, both font attributes are taken into account for the Find the Replace; in this case, the found content must be bold but not underlined or italics. The replaced content will be italics but not underlined or bold.

    Naturally, you can apply combinations of font attributes to whichever of columns 3 & 4 they're applicable to.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2016-06-26T10:05:52+00:00

    I am doing my best at it, continuously trying to make it work and feel very frustrating for not being successful or even not able to understand you, so I am sorry.

    As I said, I cannot even change for example " Transitive transitive Transitive transitive " into " Tr. tr. Tr. tr." , which for me is the most basic operation I need.

    Regarding the 4x1 table above, the third cell contains the attribute of bold font in the character <r> (U+0072), while in the fourth the attribute is being in Italics; therefore, it seems to me in well accordance with your words.

    I'd appreciate it so much to be explictly pointed to the problems in an elaborate explanation.

    Was this answer helpful?

    0 comments