How to set string in an array to the Range in Word VBA

Question

I'm writing a macro to add index entries to entries in tables in word. Some cells contain a single string that needs the entry added to which I've managed to do. Eg, cell contains 'S875'. I've used the following code for this:

For Each oRow In oTable.Rows
If oRow.Cells.count = 4 Then
    oTable.Cell(oRow.Index, 4).Select
    Selection.Expand unit:=wdCell
    oem = Left$(Selection.Text, Len(Selection.Text) - 2)

    If (oem Like "*O.E.M*") Or (oem Like "*OEM*") Then
          'ignore this row
          Debug.Print oem
   Else
     ActiveDocument.Indexes.MarkEntry Range:=Selection.Range, Entry:=oem, _
     EntryAutoText:=oem, CrossReference:="", CrossReferenceAutoText:="", 
     BookmarkName:="", Bold:=False, Italic:=False
    End If
   End If
  Next oRow

But I've got some cells that need two or more index entries added, eg, S875, 876. I've split these into an array and can loop through the array but am stuck on how to set the Range to add the index entries. What I have is:

If Len(oem) > 6 Then
     oemArray() = Split(oem, ", ")
     For i = LBound(oemArray) To UBound(oemArray)
              'need to use Indexes.MarkEntry to add an index entry for each
              ' string in the array 
              Debug.Print oemArray(i)
     Next i
End If

So I think I either need to somehow change the Selection to each entry on the array or use the Range but I'm not sure exactly?

Variatus · Accepted Answer · 2017-09-01 12:34:05Z

You were exactly on the right path. With a little bit more patience you would surely have got it done.

Sub WriteIndex()

    Dim Rng As Range
    Dim oTable As Table
    Dim oRow As Row
    Dim Oem As String
    Dim Sp() As String, i As Integer

    Set oTable = ActiveDocument.Tables(2)           ' used for my test
    For Each oRow In oTable.Rows
        ' in essence, if you have any merged cells in any row in
        ' the table your row counter will be thrown off
        If oRow.Cells.Count = 4 Then
            Set Rng = oRow.Cells(4).Range           ' avoiding the Selection object
            Rng.MoveEnd wdCharacter, -1
            Oem = Rng.Text
            ' your "Like" code seems to be case sensitive
            ' if so, this should be more flexible
            If (InStr(1, Oem, "O.E.M", vbTextCompare)) Or _
               (InStr(1, Oem, "OEM", vbTextCompare)) Then
                  'ignore this row
                  Debug.Print "Found: "; Oem
            Else
                Sp = Split(Oem, ",")
                For i = 0 To UBound(Sp)
                    With ActiveDocument
                        ' it seems that all but the first two properties
                        ' are optional and can be omitted if not set
                        .Indexes.MarkEntry Range:=Rng, _
                                           Entry:=Trim(Sp(i)), _
                                           EntryAutoText:=Trim(Sp(i)), _
                                           CrossReference:="", _
                                           CrossReferenceAutoText:="", _
                                           BookmarkName:="", _
                                           Bold:=False, _
                                           Italic:=False, _
                                           Reading:=""
                    End With
                Next i
            End If
        End If
    Next oRow
End Sub

Note that Split("S675", ",") returns an array with a single element. Therefore you can handle single indices and multiple ones with the same code.

Using the Selection object is slower than the Range object because the screen must be updated for each selection. The Range object works quietly behind the screen.

Collectives™ on Stack Overflow

How to set string in an array to the Range in Word VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related