Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using named range in vlookup
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using named range in vlookup
I've never test this but for the range to work I think you can do lik this: StockCode = Item with Sheets("STOCK") lookup1 = Application.WorksheetFunction.VLookup(Item, _ .Range(.Columns("A:a"), .Columns("e:e")), 5,False) end with Best regards -- hidek ----------------------------------------------------------------------- hideki's Profile: http://www.excelforum.com/member.php...fo&userid=1890 View this thread: http://www.excelforum.com/showthread.php?threadid=40139 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup, concatenated named range | Excel Worksheet Functions | |||
VLOOKUP with a Named Range argument | Excel Worksheet Functions | |||
Vlookup in a named range | Excel Worksheet Functions | |||
Vlookup in large named range | Excel Worksheet Functions | |||
Vlookup on named range | Excel Programming |