Index 9 error after xlWhole although content is there-CodePudding

I am trying to search the whole column A of an Excel spreadsheet in an Outlook VBA macro with the following code and have the value from column K output in the same row.

The following code works fine:

ColumnA = InputBox("Please Column A value.")
ColumnB = wb.Worksheets("fsgksdhgks").Range("A:A").Find(ColumnA).Offset(0, 10).Value
MsgBox (ColumnB)

However, the following code, which I actually need because I only want unique values (e.g., I don't want to find ABC when I enter BC), throws Error 9 Index errors.

ColumnA = InputBox("Please Column A value.")
ColumnB = wb.Worksheets("fsgksdhgks").Range("A:A").Find(What:=ColumnA, LookAt:=xlWhole).Offset(0, 10).Value
MsgBox (ColumnB)

Does anyone have any ideas?

CodePudding user response：

With late binding to excel the constants are not defined so use the value 1 in place of xlWhole. Consider checking that find returns an object to avoid further errors.

Sub demo()

    Dim ColumnA As String, ColumnB As String, rng As Range
    Dim wb As Workbook: Set wb = ThisWorkbook ' amend to suit

    ColumnA = InputBox("Please Column A value.")
    Set rng = wb.Worksheets("fsgksdhgks").Range("A:A").Find(What:=ColumnA, LookAt:=1) ' 1=xlWhole
    If rng Is Nothing Then
        MsgBox ColumnA & " not found", vbExclamation
    Else
        ColumnB = rng.Offset(0, 10).Value
        MsgBox ColumnB, vbInformation, ColumnA & "  found at " & rng.Address
    End If

End Sub