ok one other thing i found..... this post was made on another site... it looks like his vba is close to a solution that you are looking for but may need a tweak... you can try posting the code here and see if someone can fix it for oyu
Greetings to one and all.
I am trying to code a VBA function to use VLOOKUP to return a description based on a part number. The part numbers and descriptions are in another workbook (which has multiple worksheets) from the one where the created function will reside. Here is what I have so far (from somewhere on the net):
Function FindDescription(Look_Value As Variant, Tble_Array As Range, Col_num As Integer, Optional Range_look As Boolean)
Dim wSheet As Worksheet
Dim vFound
On Error Resume Next
For Each wSheet In Workbooks("K:\Data\pndb.xls").Worksheets
With wSheet
Set Tble_Array = .Range(Tble_Array.Address)
vFound = WorksheetFunction.VLookup(Look_Value, Tble_Array, Col_num, Range_look)
End With
If Not IsEmpty(vFound) Then Exit For
Next wSheet
Set Tble_Array = Nothing
FindDescription = vFound
End Function
The file K:\Data\pndb.xls is on a network drive and contains all the information. A local file will contain the FindDescription function and should return a description based on a part number input.
My problem is that the function just returns a zero value instead of the actual description. I am a VBA newbie so maybe I made some error in the code?
Thanks much for any help on this.