Using named range in vlookup

[lasca]

I an trying to create a function using a named range refering to another
sheet than the active sheet i am working on.

The problem i seem to have is if the named range is on the active sheet it
is woring fine, however if it refers to another sheet it returns a value of
"0". My situation is that i need to do a vlookup on a stock code and return a
cost value from a sheet called stock and the named range is also stock.

My cuurent sheet name is the same as my stock code that a an working on.

On using the vlookup in a normal subroutine to check if the stock code is in
the list it works fine. It is only in my function that it is not working
correct.

Here is the code for the function i have written so for.

Function matcostcalc(Stype, Item)

Dim StockCode As String
Dim Range1 As String
Dim lookup1 As String


StockCode = Item
lookup1 = Application.WorksheetFunction.VLookup(Item,
Range(Sheets("STOCK").Columns("A:a"), Sheets("STOCK").Columns("e:e")), 5,
False)


Application.Volatile True
'stockmaster

'calculate the cost for each product. depending on if it is bill of
material
'or manufactured item

Select Case Stype

Case "BILL"

matcostcalc = Application.WorksheetFunction.Sum(Range(Item))

Case "b/o"
'=VLOOKUP(C8,stock,5,FALSE)

matcostcalc = Application.WorksheetFunction.VLookup(Item,
Range(Sheets ("STOCK").Columns("A:a"), Sheets("STOCK").Columns("e:e")), 5,
False)

Case "labour"

matcostcalc = Application.WorksheetFunction.VLookup(Item,
Range(Sheets("STOCK").Columns("A:a"), Sheets("STOCK").Columns("e:e")), 5,
False)

End Select
End Function

[hideki]

I've never test this but for the range to work I think you can do like this:

StockCode = Item
with Sheets("STOCK")
lookup1 = Application.WorksheetFunction.VLookup(Item, _
.Range(.Columns("A:a"), .Columns("e:e")), 5,False)
end with

Best regards,