CODEWITHSUNDEEP
excelvbavlookup
Clarkee013
Clarkee013

Reputation: 25

VBA runtime error 1004 - vlookup works sometimes

The code below gets an input from an excel userform, looks up that value and pre-fills other fields in the userform.

It works happily but now this morning it seems that only some values of DealID's work and the rest give the following error:

runtime error1004 unable to get vlookup property of the worksheetfunction class

When I debug and hover the mouse over the code it gives the data I'm expecting to see in the fields.

Any advice would be great.

Private Sub GetDetailsButton_Click()

Dim xRg As Range
Set xRg = Worksheets("Deal List").Range("A:U")
Dim DealID As Long

DealID = Me.DealIDBox.Value

Me.AgentNameBox.Text = Application.WorksheetFunction.VLookup(DealID, xRg, 11, False)
Me.LineManagerNameBox.Text = Application.WorksheetFunction.VLookup(DealID, xRg, 12, False)
Me.CustomerRefBox.Text = Application.WorksheetFunction.VLookup(DealID, xRg, 8, False)
Me.ComplexMarkerBox.Text = Application.WorksheetFunction.VLookup(DealID, xRg, 15, False)
Me.DealTimeStampBox.Text = Format(Application.WorksheetFunction.VLookup(DealID, xRg, 3, False), "dd/mm/yy @ hh:mm:ss")
Me.EpicBox.Text = Application.WorksheetFunction.VLookup(DealID, xRg, 14, False)
Me.StockBox.Text = Application.WorksheetFunction.VLookup(DealID, xRg, 17, False)
Me.QuantityBox.Text = Application.WorksheetFunction.VLookup(DealID, xRg, 18, False)
Me.PriceBox.Text = Application.WorksheetFunction.VLookup(DealID, xRg, 19, False)
Me.GrossBox.Text = Application.WorksheetFunction.VLookup(DealID, xRg, 20, False)
Me.DateOfCallBox.Text = Format(Application.WorksheetFunction.VLookup(DealID, xRg, 4, False), "dd/mm/yy")


End Sub

Upvotes: 0

Views: 132

Answers (1)

Rory
Rory

Reputation: 34045

It's really inefficient to keep looking up the same value each time just to access a different column, so I suggest you use Match to find the row you need and then just access those cells directly:

Private Sub GetDetailsButton_Click()

    Dim xRg As Range
    Set xRg = Worksheets("Deal List").Range("A:U")
    Dim DealID As Long
    Dim matchPos

    DealID = Me.DealIDBox.Value
    matchPos = Application.Match(DealID, xRg.Columns(1), 0)
    If IsError(matchPos) Then
        matchPos = Application.Match(CStr(DealID), xRg.Columns(1), 0)
        If IsError(matchPos) Then
            MsgBox DealID & " not found"
            Exit Sub
        End If
    End If

    Me.AgentNameBox.Text = xRg.Cells(matchPos, 11).Value
    Me.LineManagerNameBox.Text = xRg.Cells(matchPos, 12).Value
    Me.CustomerRefBox.Text = xRg.Cells(matchPos, 8).Value
    Me.ComplexMarkerBox.Text = xRg.Cells(matchPos, 15).Value
    Me.DealTimeStampBox.Text = Format(xRg.Cells(matchPos, 3).Value, "dd/mm/yy @ hh:mm:ss")
    Me.EpicBox.Text = xRg.Cells(matchPos, 14).Value
    Me.StockBox.Text = xRg.Cells(matchPos, 17).Value
    Me.QuantityBox.Text = xRg.Cells(matchPos, 18).Value
    Me.PriceBox.Text = xRg.Cells(matchPos, 19).Value
    Me.GrossBox.Text = xRg.Cells(matchPos, 20).Value
    Me.DateOfCallBox.Text = Format(xRg.Cells(matchPos, 4).Value, "dd/mm/yy")

End Sub

Based on your error, I'd say some of the ID numbers in your xRg table are stored as text, which is why the lookup failed, so I've added code to search for the text version too if necessary.

Upvotes: 3

Related Questions