Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
user defined function | Excel Worksheet Functions | |||
user defined function | Excel Worksheet Functions | |||
vlookup, add parameter, on error return user defined value | Excel Worksheet Functions | |||
User Defined Function | Excel Discussion (Misc queries) | |||
User defined function | New Users to Excel |