VBA VLookup, Refer to Named Range Compile Error

[Emeng]

Hi all

I’m having trouble with the syntax to refer to a named range in ThisWorkbook when attempting a VLOOKUP from another (newly created, ActiveWorkbook) workbook.

Specifically I receive a Compile Error: object required message on the Set myVLookupResult line.

Any help is much appreciated.

Thanks & regards

Mark

Sub Lookup_Consumables()

    Dim myLookupValue As Range
    Dim myNamedRange As Range
    Dim myVLookupResult As Long

    Dim book1 As Workbook
    Dim book2 As Workbook
   
    Set book1 = ThisWorkbook
    Set book2 = ActiveWorkbook

    Set myLookupValue = book2.Sheets(2).Cells(3, 1)
    Set myNamedRange = book1.Sheets(2).Range("Consumables").Value
   
    Set myVLookupResult = WorksheetFunction.VLookup(myLookupValue, myNamedRange, 3, False)

    Dim ws As Worksheet
    Dim myRange As Range
   
    Set ws = book2.Sheets(2)
        With ws
            .Range(Columns(11), Columns(12)).ClearContents
            .Range("L3:L" & lr).Formula = myVLookupResult
        End With
End Sub

 

[Ramesh]

Hi Emeng,

The only issue, I have noticed in the above code is, you are trying to assign a value to a variable using set keyword, which is not required..hence you are receiving this error..

Try replace the following piece of code:

Set myVLookupResult = WorksheetFunction.VLookup(myLookupValue,myNamedRange, 3, False)

with:

myVLookupResult = WorksheetFunction.VLookup(myLookupValue, myNamedRange, 3, False)

I hope that should fix this issue...

Thanks,
Ram

 

[Emeng]

Hi Ram, thanks for your reply.

Removing 'Set' causes a 424 run-time error: Object required.

Mark

 

[Emeng]

Hi Ram, thanks for your reply.

Removing 'Set' causes a 424 run-time error: Object required on the line

Set myNamedRange = book1.Sheets(2).Range("Consumables").Value

Rergards

Mark

 

[NARAYANK991]

Hi ,

The posted code has more than one problem.

Can you indicate what the VBA variable myLookupValue is supposed to contain ?

Narayan

 

[Emeng]

Hi Narayan

I'm looking for numbers between 40000000 & 49999999 located in the ActiveWorkbook.Sheets(2).Columns(1).

I am now wondering whether it's variable should be Long rather than Range.

Regards

Mark

 

[Debaser]

Set myNamedRange = book1.Sheets(2).Range("Consumables").Value

should be just:

Set myNamedRange = book1.Sheets(2).Range("Consumables")

 

[Emeng]

Hi Debaser
No luck I'm afraid... the code now breaks at

myVLookupResult = WorksheetFunction.VLookup(myLookupValue, myNamedRange, 3, False)

with a 'Unable to Get the Vlookup property of the WorksheetFunction class' message.
Regards
Mark

 

[NARAYANK991]

Hi ,

See if this is OK.

Narayan

 

[Emeng]

Thanks Narayan

You are a champion!

I'll study this code to try to understand how it does what it does.

Many thanks

Mark