User Defined Function and VLookUP
 

I am having difficulty with coding a USD that will need to
use the VLookup Function within the USD. Var1 will be
returned once the USD is executed.

Sheet#1 is the main sheet
Sheet#2 is my database sheet

In Sheet #1 The rangename "tabledata" is assigned as the cell range
a1:g100 located in sheet#2

Needing help with the syntax for two things:

FUNCTION syntax in line 1 of the USD

Var1 = WorksheetFunction.VLookup( Arg1, Arg2,Arg3,[Arg4]) syntax

Var1 is what will be returned in Sheet#1

I'm a novice excel user. Hope I have described my
dilemma. Any help or example code is greatly
appreciated. thanks


Kevin

User Defined Function and VLookUP
 

Do you mean...

Function MYVLOOKUP(lookup_value As Variant, _
lookup_array As Range, lngCol As Long, _
Optional lookup_type As Boolean = True)

MYVLOOKUP = WorksheetFunction.VLookup(lookup_value, _
lookup_array, lngCol, lookup_type)

End Function

If this post helps click Yes
---------------
Jacob Skaria


"Kevin" wrote:

I am having difficulty with coding a USD that will need to
use the VLookup Function within the USD. Var1 will be
returned once the USD is executed.

Sheet#1 is the main sheet
Sheet#2 is my database sheet

In Sheet #1 The rangename "tabledata" is assigned as the cell range
a1:g100 located in sheet#2

Needing help with the syntax for two things:

FUNCTION syntax in line 1 of the USD

Var1 = WorksheetFunction.VLookup( Arg1, Arg2,Arg3,[Arg4]) syntax

Var1 is what will be returned in Sheet#1

I'm a novice excel user. Hope I have described my
dilemma. Any help or example code is greatly
appreciated. thanks


Kevin

User Defined Function and VLookUP
 

Jacob,
Thank you for the response. I pasted in your code.
when I call the USD the It returns #NAME?

I'm still not sure if I am passing the named ranged
TableData through in the proper syntax.

Anyother thoughts ? thanks,


--
Kevin


"Jacob Skaria" wrote:

Do you mean...

Function MYVLOOKUP(lookup_value As Variant, _
lookup_array As Range, lngCol As Long, _
Optional lookup_type As Boolean = True)

MYVLOOKUP = WorksheetFunction.VLookup(lookup_value, _
lookup_array, lngCol, lookup_type)

End Function

If this post helps click Yes
---------------
Jacob Skaria


"Kevin" wrote:

I am having difficulty with coding a USD that will need to
use the VLookup Function within the USD. Var1 will be
returned once the USD is executed.

Sheet#1 is the main sheet
Sheet#2 is my database sheet

In Sheet #1 The rangename "tabledata" is assigned as the cell range
a1:g100 located in sheet#2

Needing help with the syntax for two things:

FUNCTION syntax in line 1 of the USD

Var1 = WorksheetFunction.VLookup( Arg1, Arg2,Arg3,[Arg4]) syntax

Var1 is what will be returned in Sheet#1

I'm a novice excel user. Hope I have described my
dilemma. Any help or example code is greatly
appreciated. thanks


Kevin

User Defined Function and VLookUP
 

Try this UDF (User Defined function). From workbook launch VBE using Alt+F11.
From menu Insert a Module and paste the below function.Close and get back to
workbook and try the below formula.

Try this test...

In Sheet1
ColA ColB
Name Age
Adam 32
Marie 24
Francis 33

Try this formula in the same workbook sheet1
=MYVLOOKUP("Marie",A:B,2,0)


If this post helps click Yes
---------------
Jacob Skaria


"Kevin" wrote:

Jacob,
Thank you for the response. I pasted in your code.
when I call the USD the It returns #NAME?

I'm still not sure if I am passing the named ranged
TableData through in the proper syntax.

Anyother thoughts ? thanks,


--
Kevin


"Jacob Skaria" wrote:

Do you mean...

Function MYVLOOKUP(lookup_value As Variant, _
lookup_array As Range, lngCol As Long, _
Optional lookup_type As Boolean = True)

MYVLOOKUP = WorksheetFunction.VLookup(lookup_value, _
lookup_array, lngCol, lookup_type)

End Function

If this post helps click Yes
---------------
Jacob Skaria


"Kevin" wrote:

I am having difficulty with coding a USD that will need to
use the VLookup Function within the USD. Var1 will be
returned once the USD is executed.

Sheet#1 is the main sheet
Sheet#2 is my database sheet

In Sheet #1 The rangename "tabledata" is assigned as the cell range
a1:g100 located in sheet#2

Needing help with the syntax for two things:

FUNCTION syntax in line 1 of the USD

Var1 = WorksheetFunction.VLookup( Arg1, Arg2,Arg3,[Arg4]) syntax

Var1 is what will be returned in Sheet#1

I'm a novice excel user. Hope I have described my
dilemma. Any help or example code is greatly
appreciated. thanks


Kevin

User Defined Function and VLookUP
 

Your test example works great. This is when the
data is all on the same Sheet.

However, when the data is stored in Sheet2
and the UDF is called from from Sheet1, is
when I have the problem. I'm sure its a syntax
type mistake, I'm determined to get it working.
thank you for the help

--
Kevin


"Jacob Skaria" wrote:

Try this UDF (User Defined function). From workbook launch VBE using Alt+F11.
From menu Insert a Module and paste the below function.Close and get back to
workbook and try the below formula.

Try this test...

In Sheet1
ColA ColB
Name Age
Adam 32
Marie 24
Francis 33

Try this formula in the same workbook sheet1
=MYVLOOKUP("Marie",A:B,2,0)


If this post helps click Yes
---------------
Jacob Skaria


"Kevin" wrote:

Jacob,
Thank you for the response. I pasted in your code.
when I call the USD the It returns #NAME?

I'm still not sure if I am passing the named ranged
TableData through in the proper syntax.

Anyother thoughts ? thanks,


--
Kevin


"Jacob Skaria" wrote:

Do you mean...

Function MYVLOOKUP(lookup_value As Variant, _
lookup_array As Range, lngCol As Long, _
Optional lookup_type As Boolean = True)

MYVLOOKUP = WorksheetFunction.VLookup(lookup_value, _
lookup_array, lngCol, lookup_type)

End Function

If this post helps click Yes
---------------
Jacob Skaria


"Kevin" wrote:

I am having difficulty with coding a USD that will need to
use the VLookup Function within the USD. Var1 will be
returned once the USD is executed.

Sheet#1 is the main sheet
Sheet#2 is my database sheet

In Sheet #1 The rangename "tabledata" is assigned as the cell range
a1:g100 located in sheet#2

Needing help with the syntax for two things:

FUNCTION syntax in line 1 of the USD

Var1 = WorksheetFunction.VLookup( Arg1, Arg2,Arg3,[Arg4]) syntax

Var1 is what will be returned in Sheet#1

I'm a novice excel user. Hope I have described my
dilemma. Any help or example code is greatly
appreciated. thanks


Kevin

User Defined Function and VLookUP
 

Try this formula in the same workbook sheet2
=MYVLOOKUP("Marie",Sheet1!A:B,2,0)

If this post helps click Yes
---------------
Jacob Skaria


"Kevin" wrote:

Your test example works great. This is when the
data is all on the same Sheet.

However, when the data is stored in Sheet2
and the UDF is called from from Sheet1, is
when I have the problem. I'm sure its a syntax
type mistake, I'm determined to get it working.
thank you for the help

--
Kevin


"Jacob Skaria" wrote:

Try this UDF (User Defined function). From workbook launch VBE using Alt+F11.
From menu Insert a Module and paste the below function.Close and get back to
workbook and try the below formula.

Try this test...

In Sheet1
ColA ColB
Name Age
Adam 32
Marie 24
Francis 33

Try this formula in the same workbook sheet1
=MYVLOOKUP("Marie",A:B,2,0)


If this post helps click Yes
---------------
Jacob Skaria


"Kevin" wrote:

Jacob,
Thank you for the response. I pasted in your code.
when I call the USD the It returns #NAME?

I'm still not sure if I am passing the named ranged
TableData through in the proper syntax.

Anyother thoughts ? thanks,


--
Kevin


"Jacob Skaria" wrote:

Do you mean...

Function MYVLOOKUP(lookup_value As Variant, _
lookup_array As Range, lngCol As Long, _
Optional lookup_type As Boolean = True)

MYVLOOKUP = WorksheetFunction.VLookup(lookup_value, _
lookup_array, lngCol, lookup_type)

End Function

If this post helps click Yes
---------------
Jacob Skaria


"Kevin" wrote:

I am having difficulty with coding a USD that will need to
use the VLookup Function within the USD. Var1 will be
returned once the USD is executed.

Sheet#1 is the main sheet
Sheet#2 is my database sheet

In Sheet #1 The rangename "tabledata" is assigned as the cell range
a1:g100 located in sheet#2

Needing help with the syntax for two things:

FUNCTION syntax in line 1 of the USD

Var1 = WorksheetFunction.VLookup( Arg1, Arg2,Arg3,[Arg4]) syntax

Var1 is what will be returned in Sheet#1

I'm a novice excel user. Hope I have described my
dilemma. Any help or example code is greatly
appreciated. thanks


Kevin

User Defined Function and VLookUP
 

Jason, this has it working now. i was putting
(Sheet!A:B), syntax wasn't working. thank you
for your assistance.

do you know if there is a method to hide
the formulas in a spreadsheet so they are
not visible?

Thanks again.
Kevin


--
Kevin


"Jacob Skaria" wrote:

Try this formula in the same workbook sheet2
=MYVLOOKUP("Marie",Sheet1!A:B,2,0)

If this post helps click Yes
---------------
Jacob Skaria


"Kevin" wrote:

Your test example works great. This is when the
data is all on the same Sheet.

However, when the data is stored in Sheet2
and the UDF is called from from Sheet1, is
when I have the problem. I'm sure its a syntax
type mistake, I'm determined to get it working.
thank you for the help

--
Kevin


"Jacob Skaria" wrote:

Try this UDF (User Defined function). From workbook launch VBE using Alt+F11.
From menu Insert a Module and paste the below function.Close and get back to
workbook and try the below formula.

Try this test...

In Sheet1
ColA ColB
Name Age
Adam 32
Marie 24
Francis 33

Try this formula in the same workbook sheet1
=MYVLOOKUP("Marie",A:B,2,0)


If this post helps click Yes
---------------
Jacob Skaria


"Kevin" wrote:

Jacob,
Thank you for the response. I pasted in your code.
when I call the USD the It returns #NAME?

I'm still not sure if I am passing the named ranged
TableData through in the proper syntax.

Anyother thoughts ? thanks,


--
Kevin


"Jacob Skaria" wrote:

Do you mean...

Function MYVLOOKUP(lookup_value As Variant, _
lookup_array As Range, lngCol As Long, _
Optional lookup_type As Boolean = True)

MYVLOOKUP = WorksheetFunction.VLookup(lookup_value, _
lookup_array, lngCol, lookup_type)

End Function

If this post helps click Yes
---------------
Jacob Skaria


"Kevin" wrote:

I am having difficulty with coding a USD that will need to
use the VLookup Function within the USD. Var1 will be
returned once the USD is executed.

Sheet#1 is the main sheet
Sheet#2 is my database sheet

In Sheet #1 The rangename "tabledata" is assigned as the cell range
a1:g100 located in sheet#2

Needing help with the syntax for two things:

FUNCTION syntax in line 1 of the USD

Var1 = WorksheetFunction.VLookup( Arg1, Arg2,Arg3,[Arg4]) syntax

Var1 is what will be returned in Sheet#1

I'm a novice excel user. Hope I have described my
dilemma. Any help or example code is greatly
appreciated. thanks


Kevin

User Defined Function and VLookUP
 

Try the below
--Select the cells in which you want users to enter data, then go to
FormatCellsProtection tab and uncheck 'Lock'.
--Select the formulas you want to be hidden. Go to Format - Cells -
Protection and select Hidden.
--From Tools ProtectionProtect Sheet. This will allow users to
enter data, but not change or see the formulas

If this post helps click Yes
---------------
Jacob Skaria


"Kevin" wrote:

Jason, this has it working now. i was putting
(Sheet!A:B), syntax wasn't working. thank you
for your assistance.

do you know if there is a method to hide
the formulas in a spreadsheet so they are
not visible?

Thanks again.
Kevin


--
Kevin


"Jacob Skaria" wrote:

Try this formula in the same workbook sheet2
=MYVLOOKUP("Marie",Sheet1!A:B,2,0)

If this post helps click Yes
---------------
Jacob Skaria


"Kevin" wrote:

Your test example works great. This is when the
data is all on the same Sheet.

However, when the data is stored in Sheet2
and the UDF is called from from Sheet1, is
when I have the problem. I'm sure its a syntax
type mistake, I'm determined to get it working.
thank you for the help

--
Kevin


"Jacob Skaria" wrote:

Try this UDF (User Defined function). From workbook launch VBE using Alt+F11.
From menu Insert a Module and paste the below function.Close and get back to
workbook and try the below formula.

Try this test...

In Sheet1
ColA ColB
Name Age
Adam 32
Marie 24
Francis 33

Try this formula in the same workbook sheet1
=MYVLOOKUP("Marie",A:B,2,0)


If this post helps click Yes
---------------
Jacob Skaria


"Kevin" wrote:

Jacob,
Thank you for the response. I pasted in your code.
when I call the USD the It returns #NAME?

I'm still not sure if I am passing the named ranged
TableData through in the proper syntax.

Anyother thoughts ? thanks,


--
Kevin


"Jacob Skaria" wrote:

Do you mean...

Function MYVLOOKUP(lookup_value As Variant, _
lookup_array As Range, lngCol As Long, _
Optional lookup_type As Boolean = True)

MYVLOOKUP = WorksheetFunction.VLookup(lookup_value, _
lookup_array, lngCol, lookup_type)

End Function

If this post helps click Yes
---------------
Jacob Skaria


"Kevin" wrote:

I am having difficulty with coding a USD that will need to
use the VLookup Function within the USD. Var1 will be
returned once the USD is executed.

Sheet#1 is the main sheet
Sheet#2 is my database sheet

In Sheet #1 The rangename "tabledata" is assigned as the cell range
a1:g100 located in sheet#2

Needing help with the syntax for two things:

FUNCTION syntax in line 1 of the USD

Var1 = WorksheetFunction.VLookup( Arg1, Arg2,Arg3,[Arg4]) syntax

Var1 is what will be returned in Sheet#1

I'm a novice excel user. Hope I have described my
dilemma. Any help or example code is greatly
appreciated. thanks


Kevin

User Defined Function and VLookUP
 

Thank you, all is working great.
--
Kevin


"Jacob Skaria" wrote:

Try the below
--Select the cells in which you want users to enter data, then go to
FormatCellsProtection tab and uncheck 'Lock'.
--Select the formulas you want to be hidden. Go to Format - Cells -
Protection and select Hidden.
--From Tools ProtectionProtect Sheet. This will allow users to
enter data, but not change or see the formulas

If this post helps click Yes
---------------
Jacob Skaria


"Kevin" wrote:

Jason, this has it working now. i was putting
(Sheet!A:B), syntax wasn't working. thank you
for your assistance.

do you know if there is a method to hide
the formulas in a spreadsheet so they are
not visible?

Thanks again.
Kevin


--
Kevin


"Jacob Skaria" wrote:

Try this formula in the same workbook sheet2
=MYVLOOKUP("Marie",Sheet1!A:B,2,0)

If this post helps click Yes
---------------
Jacob Skaria


"Kevin" wrote:

Your test example works great. This is when the
data is all on the same Sheet.

However, when the data is stored in Sheet2
and the UDF is called from from Sheet1, is
when I have the problem. I'm sure its a syntax
type mistake, I'm determined to get it working.
thank you for the help

--
Kevin


"Jacob Skaria" wrote:

Try this UDF (User Defined function). From workbook launch VBE using Alt+F11.
From menu Insert a Module and paste the below function.Close and get back to
workbook and try the below formula.

Try this test...

In Sheet1
ColA ColB
Name Age
Adam 32
Marie 24
Francis 33

Try this formula in the same workbook sheet1
=MYVLOOKUP("Marie",A:B,2,0)


If this post helps click Yes
---------------
Jacob Skaria


"Kevin" wrote:

Jacob,
Thank you for the response. I pasted in your code.
when I call the USD the It returns #NAME?

I'm still not sure if I am passing the named ranged
TableData through in the proper syntax.

Anyother thoughts ? thanks,


--
Kevin


"Jacob Skaria" wrote:

Do you mean...

Function MYVLOOKUP(lookup_value As Variant, _
lookup_array As Range, lngCol As Long, _
Optional lookup_type As Boolean = True)

MYVLOOKUP = WorksheetFunction.VLookup(lookup_value, _
lookup_array, lngCol, lookup_type)

End Function

If this post helps click Yes
---------------
Jacob Skaria


"Kevin" wrote:

I am having difficulty with coding a USD that will need to
use the VLookup Function within the USD. Var1 will be
returned once the USD is executed.

Sheet#1 is the main sheet
Sheet#2 is my database sheet

In Sheet #1 The rangename "tabledata" is assigned as the cell range
a1:g100 located in sheet#2

Needing help with the syntax for two things:

FUNCTION syntax in line 1 of the USD

Var1 = WorksheetFunction.VLookup( Arg1, Arg2,Arg3,[Arg4]) syntax

Var1 is what will be returned in Sheet#1

I'm a novice excel user. Hope I have described my
dilemma. Any help or example code is greatly
appreciated. thanks


Kevin

User Defined Function and VLookUP
 

I did have one more question. My spreadsheet is using a couple
of pull down menus. I used the Data Validation, List to generate
the menus. They work fine. However, I noticed if I want the
text within the menu items to have a different color font, the
text is always going back to a default font color of black.
Any ideas on how to change the font color? thank you again.
Kevin

User Defined Function and VLookUP
 

Hi,

It is easy to see the formula even after applying protection as suggested
below. Just copy a blank cell and then Edit paste Special Formats over
the cell which is protected. The formula will be visible (even though the
cell will be protected [cannot edit, delete the contents]).

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Jacob Skaria" wrote in message
...
Try the below
--Select the cells in which you want users to enter data, then go to
FormatCellsProtection tab and uncheck 'Lock'.
--Select the formulas you want to be hidden. Go to Format - Cells -
Protection and select Hidden.
--From Tools ProtectionProtect Sheet. This will allow users to
enter data, but not change or see the formulas

If this post helps click Yes
---------------
Jacob Skaria


"Kevin" wrote:

Jason, this has it working now. i was putting
(Sheet!A:B), syntax wasn't working. thank you
for your assistance.

do you know if there is a method to hide
the formulas in a spreadsheet so they are
not visible?

Thanks again.
Kevin


--
Kevin


"Jacob Skaria" wrote:

Try this formula in the same workbook sheet2
=MYVLOOKUP("Marie",Sheet1!A:B,2,0)

If this post helps click Yes
---------------
Jacob Skaria


"Kevin" wrote:

Your test example works great. This is when the
data is all on the same Sheet.

However, when the data is stored in Sheet2
and the UDF is called from from Sheet1, is
when I have the problem. I'm sure its a syntax
type mistake, I'm determined to get it working.
thank you for the help

--
Kevin


"Jacob Skaria" wrote:

Try this UDF (User Defined function). From workbook launch VBE
using Alt+F11.
From menu Insert a Module and paste the below function.Close and
get back to
workbook and try the below formula.

Try this test...

In Sheet1
ColA ColB
Name Age
Adam 32
Marie 24
Francis 33

Try this formula in the same workbook sheet1
=MYVLOOKUP("Marie",A:B,2,0)


If this post helps click Yes
---------------
Jacob Skaria


"Kevin" wrote:

Jacob,
Thank you for the response. I pasted in your code.
when I call the USD the It returns #NAME?

I'm still not sure if I am passing the named ranged
TableData through in the proper syntax.

Anyother thoughts ? thanks,


--
Kevin


"Jacob Skaria" wrote:

Do you mean...

Function MYVLOOKUP(lookup_value As Variant, _
lookup_array As Range, lngCol As Long, _
Optional lookup_type As Boolean = True)

MYVLOOKUP = WorksheetFunction.VLookup(lookup_value, _
lookup_array, lngCol, lookup_type)

End Function

If this post helps click Yes
---------------
Jacob Skaria


"Kevin" wrote:

I am having difficulty with coding a USD that will need to
use the VLookup Function within the USD. Var1 will be
returned once the USD is executed.

Sheet#1 is the main sheet
Sheet#2 is my database sheet

In Sheet #1 The rangename "tabledata" is assigned as the
cell range
a1:g100 located in sheet#2

Needing help with the syntax for two things:

FUNCTION syntax in line 1 of the USD

Var1 = WorksheetFunction.VLookup( Arg1, Arg2,Arg3,[Arg4])
syntax

Var1 is what will be returned in Sheet#1

I'm a novice excel user. Hope I have described my
dilemma. Any help or example code is greatly
appreciated. thanks


Kevin

User Defined Function and VLookUP
 

Thanks for the tip, I never thought of this.
Kevin
--
Kevin


"Ashish Mathur" wrote:

Hi,

It is easy to see the formula even after applying protection as suggested
below. Just copy a blank cell and then Edit paste Special Formats over
the cell which is protected. The formula will be visible (even though the
cell will be protected [cannot edit, delete the contents]).

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Jacob Skaria" wrote in message
...
Try the below
--Select the cells in which you want users to enter data, then go to
FormatCellsProtection tab and uncheck 'Lock'.
--Select the formulas you want to be hidden. Go to Format - Cells -
Protection and select Hidden.
--From Tools ProtectionProtect Sheet. This will allow users to
enter data, but not change or see the formulas

If this post helps click Yes
---------------
Jacob Skaria


"Kevin" wrote:

Jason, this has it working now. i was putting
(Sheet!A:B), syntax wasn't working. thank you
for your assistance.

do you know if there is a method to hide
the formulas in a spreadsheet so they are
not visible?

Thanks again.
Kevin


--
Kevin


"Jacob Skaria" wrote:

Try this formula in the same workbook sheet2
=MYVLOOKUP("Marie",Sheet1!A:B,2,0)

If this post helps click Yes
---------------
Jacob Skaria


"Kevin" wrote:

Your test example works great. This is when the
data is all on the same Sheet.

However, when the data is stored in Sheet2
and the UDF is called from from Sheet1, is
when I have the problem. I'm sure its a syntax
type mistake, I'm determined to get it working.
thank you for the help

--
Kevin


"Jacob Skaria" wrote:

Try this UDF (User Defined function). From workbook launch VBE
using Alt+F11.
From menu Insert a Module and paste the below function.Close and
get back to
workbook and try the below formula.

Try this test...

In Sheet1
ColA ColB
Name Age
Adam 32
Marie 24
Francis 33

Try this formula in the same workbook sheet1
=MYVLOOKUP("Marie",A:B,2,0)


If this post helps click Yes
---------------
Jacob Skaria


"Kevin" wrote:

Jacob,
Thank you for the response. I pasted in your code.
when I call the USD the It returns #NAME?

I'm still not sure if I am passing the named ranged
TableData through in the proper syntax.

Anyother thoughts ? thanks,


--
Kevin


"Jacob Skaria" wrote:

Do you mean...

Function MYVLOOKUP(lookup_value As Variant, _
lookup_array As Range, lngCol As Long, _
Optional lookup_type As Boolean = True)

MYVLOOKUP = WorksheetFunction.VLookup(lookup_value, _
lookup_array, lngCol, lookup_type)

End Function

If this post helps click Yes
---------------
Jacob Skaria


"Kevin" wrote:

I am having difficulty with coding a USD that will need to
use the VLookup Function within the USD. Var1 will be
returned once the USD is executed.

Sheet#1 is the main sheet
Sheet#2 is my database sheet

In Sheet #1 The rangename "tabledata" is assigned as the
cell range
a1:g100 located in sheet#2

Needing help with the syntax for two things:

FUNCTION syntax in line 1 of the USD

Var1 = WorksheetFunction.VLookup( Arg1, Arg2,Arg3,[Arg4])
syntax

Var1 is what will be returned in Sheet#1

I'm a novice excel user. Hope I have described my
dilemma. Any help or example code is greatly
appreciated. thanks


Kevin