EN VI

Excel - Find Function VBA Named Argument Not Found?

How to Excel - Find Function VBA Named Argument Not Found

I am writing a macro that runs through a Word document and highlights all the values that is contained in my cell range in Excel. I have used the Find function which works fine until I add the argument IgnoreSpace:= True, which I don't understand why it's resulting in error. Appreciate any help.

Sub Highlight()
    Dim lrow As Long
    lrow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row

    Dim i As Long
    Dim myRange As Object
    Dim FindText As Object

    Set myRange = ActiveDocument.Content

    For i = 1 To lrow Step 1
        myRange.Find.Execute FindText:=Sheet1.Range("A" & i).Value, MatchCase:=False, 
        IgnoreSpace:=True, Forward:=True, MatchWholeWord:=True

        If myRange.Find.Found = True Then myRange.HighlightColorIndex = wdYellow
    Next i
End Sub

Solution:

Highlight Strings in Word Document From Excel

If you use early binding, you will see that you cannot use the IgnoreSpace argument with the Execute method in the way you have tried hence the error.
You could use it with the Execute2007 method though.
IMO, you were going down the wrong road either way.
Note that I'm a newb in Word but after some investigating I came up with the following.

Option Explicit

Sub HighlightInWord()
    
     ' Late Binding (not recommended because you don't have access
     ' to the Word Intellisense, you have to replace the word constants
     ' with numbers,...)
'    Dim wdApp As Object: Set wdApp = GetObject(, "Word.Application")
'    Dim wdDoc As Object: Set wdDoc = wdApp.ActiveDocument
'    Dim wdRange As Object: Set wdRange = wdApp.ActiveDocument.Content
    
    ' Early Binding (Recommended)
    ' Needs a reference to
    '     'Tools->References->Microsoft Word ??.0 Object Library'
    
    ' Attempt to create a reference to an instance of Word.
    Dim wdApp As Word.Application:
    On Error Resume Next
        Set wdApp = GetObject(, "Word.Application")
    On Error GoTo 0
    
    ' Check if the reference to an instance of Word was not created.
    If wdApp Is Nothing Then
        MsgBox "Word is not open!", vbExclamation
        Exit Sub
    End If
    
    ' Check if there is no open Word document.
    If wdApp.Documents.Count = 0 Then
        MsgBox "No open Word document found!", vbExclamation
        Exit Sub
    End If
    
    Dim wdDoc As Word.Document: Set wdDoc = wdApp.ActiveDocument
    Dim wdRange As Word.Range: Set wdRange = wdDoc.Content
    
     ' Store the current default highlight color in a variable.
    Dim DefaultColor As Long:
    DefaultColor = wdApp.Options.DefaultHighlightColorIndex
    wdApp.Options.DefaultHighlightColorIndex = wdYellow ' 7 ' wdYellow
    
    Dim lRow As Long: lRow = Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row
    
    Application.ScreenUpdating = False
    
    Dim i As Long, CellString As String
     
    With wdRange
        ' Clear existing highlights.
        .HighlightColorIndex = wdNoHighlight ' 0 ' wdNoHighlight
        With .Find
            .MatchCase = False
            .MatchWholeWord = True
            .Replacement.ClearFormatting
            .Replacement.Highlight = True
            .IgnoreSpace = True
            For i = 1 To lRow
                CellString = CStr(Sheet1.Cells(i, "A").Value)
                If Len(CellString) > 0 Then ' the cell is not blank
                    .Text = CellString
                    .Execute Replace:=wdReplaceAll ' 2 ' wdReplaceAll
                'Else ' the cell is blank; do nothing
                End If
            Next i
        End With
    End With
    
     ' Reset the default highlight color.
    wdApp.Options.DefaultHighlightColorIndex = DefaultColor

    Application.ScreenUpdating = True

    MsgBox "Words highlighted.", vbInformation

End Sub