User Defined Function and VLookUP

[Kevin]

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

 

[Jacob Skaria]

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

 

[Kevin]

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

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

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

 

[Jacob Skaria]

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

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

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

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

 

[Kevin]

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

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

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

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


:

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

 

[Jacob Skaria]

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

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

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

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

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


:

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


:

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

 

[Kevin]

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

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

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

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

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


:

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


:

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


:

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

 

[Jacob Skaria]

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

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

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

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

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

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


:

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


:

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


:

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


:

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

 

[Kevin]

Thank you, all is working great.
--
Kevin

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

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

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

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

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


:

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


:

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


:

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


:

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


:

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

 

[Kevin]

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

 

[Ashish Mathur]

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

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

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

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

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

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


:

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


:

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


:

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


:

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


:

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

 

[Kevin]

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

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

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

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

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


:

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

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


:

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


:

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


:

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


:

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


:

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