Vlookup in macro won't work

[merry_fay]

Hiya,

I know there's tons of examples of this online, but I've looked & can't for the life of me work out why my vlookup won't work. Here's my code:

Dim SC As Integer
Dim SN As String

SC = Range("S_Code").Value
SN = Application.WorksheetFunction.VLookup(SC, Range("S_Code_R"), 2, False)


SN is to be used to set the value of cell "S_Name".
I've tried it using Range("S_Code").Value, Range("S_Code"), "S_Code", S_Code in place of the SC but it keeps coming up with the error:

Method 'Range' of object '_Worksheet' failed

SC does have a value & is definitely included in the S_Code_R range

Any suggestions?

Thanks

 

[Sthrncali]

I think the issue is in this line:

SC = Range("S_Code").Value

Unless I am missing something, that is not a valid address to get a value from... If S_Code is a Named Range on a Worksheet, then it should be:

SC = Sheet1.Range("S_Code").Value

If its not a named range, then what is it referring to?

 

[merry_fay]

Yes, "S_Code" is a named range.

I tried adding the sheet name in & also as you typed it, but it makes that line fall over instead of the next one.

If I just run the code:

SC = Range("S_Code").Value
MsgBox SC

I get a messagebox with the value I'm expecting S_Code to be in it, so from that I assume that line of code is working fine.

What I want to do is look up the value from named range (single cell) S_Code in the named range S_Code_R & bring back a result.

Thanks

 

[merry_fay]

I've just had a bit more of a play & with this:

Dim SC As Integer
Dim SN As String
Dim SCR As Range

SC = Range("S_Code").Value
SCR = Range("'[Spreadsheet Name.xls]Lookups'!$A1:$D5000")
SN = Application.WorksheetFunction.VLookup(SC, SCR, 2, False)

It's the SCR= line which is coming up with the same error message, so it seems I'm having problems getting the macro to look at the range I want.

Any sugestions? I'm completely perplexed.

Thanks!

 

[Ruddles]

This probably doesn't help but the code in your first post worked fine here.

If it wasn't an insulting suggestion I would ask you to treble-check your named ranges.