VLOOKUP in VBA returns run-time error 424

[Brucefield]

I'm trying to use VLOOKUP in VBA to add a value to a variable:

LizTeamName = Application.WorksheetFunction.VLookup(TeamName, Range("TeamNameLookup"), 3, False).Value

The macro stops at this line with the following error

Run-time error '424': Object Required

What I'm trying to do: Range "TeamNameLookup" is in the active file, albeit in a different sheet. It's 4 columns wide, and the value I want to return is in column 3. The variable "TeamName" is populated with a value that's definitely in the first column of the named range. My feel is that for some reason Excel/VBA wants an object attached to the end of Range("TeamNameLookup"), if only because that's the only bit of the code that could possibly have an object added to it!

Any thoughts? Thanks in advance!

 

[VoG]

Try removing .Value from the end.

 

[Brucefield]

VoG, Thanks for trying...

Syntax now reads:

LizTeamName = Application.WorksheetFunction.VLookup(TeamName, Range("TeamNameLookup"), 3, False)

Error is now:

Run-time Error '1004': Method 'Range' of object '_Global' failed

As you've probably guessed, I've been tweaking the code for a while before coming online. Unfortunately I've recently moved to a different company (I'm a contractor) so my library of VBA has to be started again...

 

[Richard Schollar]

Hi Bruce

Is that a sheet-level defined name? If so, does it work if you prefix the Range() with the sheet name ie:

LizTeamName = Application.WorksheetFunction.VLookup(TeamName, Sheets("YourSheetName").Range("TeamNameLookup"), 3, False)

 

[Mike Blackman]

Hi,

Is team name defined in a variable within the code or a Defined Name as worksheet level? Is TeamNameLookup definitely 3 columns wide?

 

[Brucefield]

Richard,

You've cracked it!

VoG and Mike - thanks for your help too...

Now to try and put something back in by helping someone else, time permitting...

Thanks,

Chris (I know, it's counter-intuitive user name!)