Excel VLookup "Object Required" Error

Question

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

`vlookup` returns a value, not a range. You can't `select` your `isbn`. Try `Sheets("AAP Dashboard").Range("C4").Value = isbn` — Tim Williams, Jan 24 '14 at 18:26

score 2 · Answer 1 · edited May 23 '17 at 11:56

2

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)

edited May 23 '17 at 11:56

Community

1
1

answered Jan 24 '14 at 18:31

Dmitry Pavliv

35,333
13
79
80

score 2 · Accepted Answer · edited Jan 24 '14 at 20:00

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

Thanks @Kelvin, for a thorough explanation and for the working sample. Just what I needed. — jeremytripp, Jan 24 '14 at 20:00

Excel VLookup "Object Required" Error

2 Answers2