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 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

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
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 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" wrote in message
...
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

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" wrote in message
...
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