Assign found value, from a range, to variable

Question

I want to grab a unique value from Outlook mail, look for that value in a column of an Excel file, and return an associated value.

I am working with the .find function from the Excel library to lookup my unique value. find is supposed to return a range of the first occurrence of my value but I cannot assign that value to the variable: pointer.

How can I reference it?

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

Dave · Accepted Answer · 2016-11-28 09:50:53Z

2

Where you are trying to set an object reference you need the Set keyword. Try this:

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

You should also handle the scenario where the reference is not found. This can be done like so:

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

answered Nov 28, 2016 at 9:50

Dave

4,3562 gold badges26 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Assign found value, from a range, to variable

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