Custom Lookup Function

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

On Sat, 17 Dec 2005 08:13:56 -0000, Greg <[email protected]> wrote:

> 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



--
------------------
Darren

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

SetmyRange = Worksheets(:CCNDC_Orgs").Range(A1:M242")

--


Gary


"Greg" <[email protected]> wrote in message
news:[email protected]...
> 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

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

Darren


On Sat, 17 Dec 2005 08:26:04 -0000, Darren Hill
<[email protected]> wrote:

>
> Could this be it:
> SetmyRange = Worksheets(:CCNDC_Orgs").Range(A1:M242")
> This should be
> Set myRange = Worksheets(:CCNDC_Orgs").Range(A1:M242")
>
>
> On Sat, 17 Dec 2005 08:13:56 -0000, Greg <[email protected]> wrote:
>
>> 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
>
>
>



--
------------------
Darren

Gary Keramidas wrote:
> what is the colon for? and the out of place quotes?
>
> SetmyRange = Worksheets(:CCNDC_Orgs").Range(A1:M242")
>
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)


"Greg" <[email protected]> wrote in message
news:[email protected]...
> 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