VBA Outlook/Excel

Question

我在Outlook中编写了一个VBA子，该子将：从邮件中获取唯一值，在Excel文件的列中查找该值，然后返回关联的值。我正在使用Excel库中的.find函数来查找我在Excel中的唯一值，但是，发现应该返回我值的第一次出现的范围，但是我无法将该值分配给变量：指针。我不能引用它。任何见解都赞赏。谢谢！

Sub OTM1S() '(ByVal Item As Object)
    Dim xlApp As Object
    Dim wb As Workbook
    Dim pointer As Range
    Set xlApp = CreateObject("Excel.Application")
    Set wb = xlApp.Workbooks.Open("I:referencefile.xlsx")
    'On Error Resume Next
    pointer = wb.Sheets("Bonds").Range("A1:A10000").Find("XS1058142081")
    MsgBox pointer.Offset(0, 1)
    'On Error GoTo 0
    wb.Save
    wb.Close
End Sub

Answer 1

在尝试设置对象引用的地方需要Set关键字。尝试以下操作：

Sub OTM1S() '(ByVal Item As Object)
    Dim xlApp As Object
    Dim wb As Workbook
    Dim pointer As Range
    Set xlApp = CreateObject("Excel.Application")
    Set wb = xlApp.Workbooks.Open("I:referencefile.xlsx")
    'On Error Resume Next
    Set pointer = wb.Sheets("Bonds").Range("A1:A10000").Find("XS1058142081")
    MsgBox pointer.Offset(0, 1)
    'On Error GoTo 0
    wb.Save
    wb.Close
End Sub

您还应该处理未找到参考的方案。可以这样做：

Set pointer = wb.Sheets("Bonds").Range("A1:A10000").Find("XS1058142081")
If Not pointer Is Nothing Then
    MsgBox pointer.Offset(0,1)
Else
    MsgBox "Sorry, couldn't find that in the specified range."
End If