CODEWITHSUNDEEP
vbaexcel
jeremytripp
jeremytripp

Reputation: 1038

Excel VLookup "Object Required" Error

Here's another one of those "so simple it's maddening when it doesn't work" type questions.

Got a macro below. It's based on an ActiveX Combo box which is linked to C3. When the user makes a selection, the macro is supposed to go find that selection on another worksheet, identify the isbn (in column 2), and return to the original worksheet to paste it in the appropriate cell (C4).

Stepping through, it throws an "Object Required" error at isbn.Select.

I'm sure it's something easy. Always is. Can you give me hand?

Private Sub TitleSelection_Change()

Dim lookFor As Range
Dim rng As Range
Dim isbn As Variant

Set lookFor = Sheets("AAP Dashboard").Range("C3")
Set rng = Sheets("Books").Columns("A:I")

isbn = Application.VLookup(lookFor, rng, 2)

Application.ScreenUpdating = False

isbn.Select
Selection.Copy
Sheets("AAP Dashboard").Range("C4").Select
Selection.Copy
ActiveSheet.Paste
Application.CutCopyMode = False

Application.ScreenUpdating = True

End Sub

Upvotes: 2

Views: 8277

Answers (2)

Kelvin Dealca
Kelvin Dealca

Reputation: 305

You can't select isbn because it is variant object, it doesn't have that member.

Try:

Dim lookFor As Variant
Dim rng As Range
Dim isbn As Variant

lookFor = Sheets("AAP Dashboard").Range("C3").Value
Set rng = Sheets("Books").Columns("A:I")

isbn = Application.WorksheetFunction.VLookup(lookFor, rng, 2, FALSE)

Application.ScreenUpdating = False
Sheets("AAP Dashboard").Range("C4").Value = isbn
Application.ScreenUpdating = True

I think you are missing an object between Application and Vlookup, add "WorksheetFunction". Also note that vlookup returns the value it finds in column 2 so no need to copy and paste. Finally note that Vlookup's first argument is a value not a range object so I changed lookFor to Variant

Upvotes: 2

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

You can slightly modify your code to get desired result:

Set lookFor = Sheets("AAP Dashboard").Range("C3")
Set Rng = Sheets("Books").Columns("A:I")

isbn = Application.Match(lookFor, Rng.Columns(1))

If Not IsError(isbn) Then
    Rng.Cells(isbn, 1).Select
    'your code here
End If

Btw, try to avoid Select and Active... statements (how to avoid them)

Upvotes: 2

Related Questions