Custom Lookup Function

  • Thread starter Thread starter Greg
  • Start date Start date
G

Greg

Hi


I am trying to write a function that looks up a value from a table and
gets relevant infomation. I want to create it as an addin and have the
table to lookup included which could be 2000 lines or more.

eg

Cell Value to lookup = NG1100


Table to lookup

Code Desc Part1 ...etc
NG0100 Desc1 ABAA
NG1000 Desc2 ACAA
NG1100 Desc3 ADAA
NG1200 Desc4 AEAA
NG2000 Desc5 AFAA
NG2100 Desc6 AGAA
etc..

Now this is easy in a normal sheet

=vlookup("NG1100",Table,3,false)



The idea is that I can use it for several different tables referred to
in the custom function by giving it a variable with out me having to
load the ref table first create the vlookup etc. It would also be used
by several people but only one of them would have the responsibility of
updating the ref table and the addin. They would all use the same addin
located on a lan.


I thought I could create a table in a new workbook, create my custom
function and save it as a Excel addin but it just doesn't do anything.

Public Function findCC(LookupCentre As String)
Dim myRange as Range
SetmyRange = Worksheets(:CCNDC_Orgs").Range(A1:M242")

findCC = Application.WorksheetFunction.Vlookup(LookupCentre, myRange,
7, False)

End Function


The file looks like

The VBAProject(CCNDC_Orgs.xls)
|_Microsoft Excel Objects
|_Sheet1(CCNDC_Orgs)
|_ThisWorkbook
|_Modules
|_Module1




I then used this formula in another workbook and sheet
=findCC(A1)
and it returns nothing but an error.
When I try to debug it goes to the line then just ends.

The idea is that you use this formula in various files without having to
load individual ref file and creating lookups.

Any assistance would be appreciated in either fixing my current approach
or suggesting another way. The intent is to make it easy to use
repeatedly on different files by different people.


Thanks in anticipation, Greg
 
Could this be it:
SetmyRange = Worksheets(:CCNDC_Orgs").Range(A1:M242")
This should be
Set myRange = Worksheets(:CCNDC_Orgs").Range(A1:M242")

Darren
 
what is the colon for? and the out of place quotes?

SetmyRange = Worksheets(:CCNDC_Orgs").Range(A1:M242")
 
Oh yes, as Gary points out, that should be
Set myRange = Worksheets("CCNDC_Orgs").Range("A1:M242")

Darren
 
Gary said:
what is the colon for? and the out of place quotes?

SetmyRange = Worksheets(:CCNDC_Orgs").Range(A1:M242")
Click to expand...
Sorry my typo errors this is what is in the code

Set myRange = Worksheets("CCNDC_Orgs").Range("A1:M242")


Regards


Greg
 
Greg,

The corrected function works just fine.

What data do you have in the table, and what lookup value?

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Back
Top