|
Vlookup - Issue....
Hi,
I need help.... I have a worksheet which looks like this... StoreNo--- 201 202 203 Week 1 X Week 2 y Week 3 z I have two combobox's on main sheet where users can select Week's and Store numbers. So say I can select Week 1 and Store 201. Once users have selected these two variables , they click on a button and the corresponding data should be populated in a textbox . So when I click the button , the output in textbox should be 'X' for above example. I have the following code in button click event: Dim strStoreNumber As String Dim strAccountWeek As String Dim rngLookup As Range Dim varVlookupVarient As Variant Private Sub CommandButton1_Click() strStoreNumber = ComboBox1.Value strAccountWeek = ComboBox2.Value Set rngLookup = Application.Workbooks("POS.xls").Worksheets("Sheet 1").Range("A:DW") ' this doesnt work.... varVlookupVarient = Application.VLookup(strAccountWeek, rngLookup, ComboBox1.Value, False) ' Below line works but I need the column in Vlookup to be dynamically selected..... 'varVlookupVarient = Application.VLookup(strAccountWeek, rngLookup, 2, False) MsgBox ("This is what you get" & varVlookupVarient) ' Once I get this data right, I can then have this as TextBox.value. End Sub Please help..... I am not sure if I am doing this right...is there another way instead of using Vlookups... Regards Veeraan |
Vlookup - Issue....
Hi,
Say you have named your ranges on the sheet: - Data: named range for the entire table - Stores: for the top row of Data - Weeks: for the leftmost column of Data Set the LinkedCell propertry of the comboboxes so the selected vlaues are sent dfirectly to the sheet (select a combobox on the sheet, click the Properties icon from the COntrolToolbox toolbar, set LinkedCell) - for combobox 1, say: D1 - for combobx 2 , say: D2 (or on another hidden sheet if you prefer) Now to get the result, use the INDEX function: INDEX( Table , row_index, column_index) and use the MATCH function to get these indexes within the table. So, in a cell = INDEX(Data, Match(D2,Weeks,0), Match(Value(D1), Stores,0) ) Note: I use Value(D1) instead of just D1 because the combo's returned value is a string that is, returned string '201' would not match number 201. Using VALUE('201') makes it work. Now, no need of a button to search the table... when the user makes a choice with the combo's the table value is immediately computed. Regards, Sébastien <http://www.ondemandanalysis.com "veeraan" wrote: Hi, I need help.... I have a worksheet which looks like this... StoreNo--- 201 202 203 Week 1 X Week 2 y Week 3 z I have two combobox's on main sheet where users can select Week's and Store numbers. So say I can select Week 1 and Store 201. Once users have selected these two variables , they click on a button and the corresponding data should be populated in a textbox . So when I click the button , the output in textbox should be 'X' for above example. I have the following code in button click event: Dim strStoreNumber As String Dim strAccountWeek As String Dim rngLookup As Range Dim varVlookupVarient As Variant Private Sub CommandButton1_Click() strStoreNumber = ComboBox1.Value strAccountWeek = ComboBox2.Value Set rngLookup = Application.Workbooks("POS.xls").Worksheets("Sheet 1").Range("A:DW") ' this doesnt work.... varVlookupVarient = Application.VLookup(strAccountWeek, rngLookup, ComboBox1.Value, False) ' Below line works but I need the column in Vlookup to be dynamically selected..... 'varVlookupVarient = Application.VLookup(strAccountWeek, rngLookup, 2, False) MsgBox ("This is what you get" & varVlookupVarient) ' Once I get this data right, I can then have this as TextBox.value. End Sub Please help..... I am not sure if I am doing this right...is there another way instead of using Vlookups... Regards Veeraan |
Vlookup - Issue....
strAccountWeek = ComboBox2.Value
The "Value" of a combobox is what is displayed in it. But Vlookup needs an 'offset' number. So you should use the combo box's ListIndex property (+1) rather than its Value property. -- Jim "veeraan" wrote in message ... Hi, I need help.... I have a worksheet which looks like this... StoreNo--- 201 202 203 Week 1 X Week 2 y Week 3 z I have two combobox's on main sheet where users can select Week's and Store numbers. So say I can select Week 1 and Store 201. Once users have selected these two variables , they click on a button and the corresponding data should be populated in a textbox . So when I click the button , the output in textbox should be 'X' for above example. I have the following code in button click event: Dim strStoreNumber As String Dim strAccountWeek As String Dim rngLookup As Range Dim varVlookupVarient As Variant Private Sub CommandButton1_Click() strStoreNumber = ComboBox1.Value strAccountWeek = ComboBox2.Value Set rngLookup = Application.Workbooks("POS.xls").Worksheets("Sheet 1").Range("A:DW") ' this doesnt work.... varVlookupVarient = Application.VLookup(strAccountWeek, rngLookup, ComboBox1.Value, False) ' Below line works but I need the column in Vlookup to be dynamically selected..... 'varVlookupVarient = Application.VLookup(strAccountWeek, rngLookup, 2, False) MsgBox ("This is what you get" & varVlookupVarient) ' Once I get this data right, I can then have this as TextBox.value. End Sub Please help..... I am not sure if I am doing this right...is there another way instead of using Vlookups... Regards Veeraan |
Vlookup - Issue....
Thanks Sebastienm.....
It works and is much simpler... "sebastienm" wrote: Hi, Say you have named your ranges on the sheet: - Data: named range for the entire table - Stores: for the top row of Data - Weeks: for the leftmost column of Data Set the LinkedCell propertry of the comboboxes so the selected vlaues are sent dfirectly to the sheet (select a combobox on the sheet, click the Properties icon from the COntrolToolbox toolbar, set LinkedCell) - for combobox 1, say: D1 - for combobx 2 , say: D2 (or on another hidden sheet if you prefer) Now to get the result, use the INDEX function: INDEX( Table , row_index, column_index) and use the MATCH function to get these indexes within the table. So, in a cell = INDEX(Data, Match(D2,Weeks,0), Match(Value(D1), Stores,0) ) Note: I use Value(D1) instead of just D1 because the combo's returned value is a string that is, returned string '201' would not match number 201. Using VALUE('201') makes it work. Now, no need of a button to search the table... when the user makes a choice with the combo's the table value is immediately computed. Regards, Sébastien <http://www.ondemandanalysis.com "veeraan" wrote: Hi, I need help.... I have a worksheet which looks like this... StoreNo--- 201 202 203 Week 1 X Week 2 y Week 3 z I have two combobox's on main sheet where users can select Week's and Store numbers. So say I can select Week 1 and Store 201. Once users have selected these two variables , they click on a button and the corresponding data should be populated in a textbox . So when I click the button , the output in textbox should be 'X' for above example. I have the following code in button click event: Dim strStoreNumber As String Dim strAccountWeek As String Dim rngLookup As Range Dim varVlookupVarient As Variant Private Sub CommandButton1_Click() strStoreNumber = ComboBox1.Value strAccountWeek = ComboBox2.Value Set rngLookup = Application.Workbooks("POS.xls").Worksheets("Sheet 1").Range("A:DW") ' this doesnt work.... varVlookupVarient = Application.VLookup(strAccountWeek, rngLookup, ComboBox1.Value, False) ' Below line works but I need the column in Vlookup to be dynamically selected..... 'varVlookupVarient = Application.VLookup(strAccountWeek, rngLookup, 2, False) MsgBox ("This is what you get" & varVlookupVarient) ' Once I get this data right, I can then have this as TextBox.value. End Sub Please help..... I am not sure if I am doing this right...is there another way instead of using Vlookups... Regards Veeraan |
Vlookup - Issue....
Thanks Jim...
Your method works too... I added the following lines..to my code... strTest = ComboBox1.ListIndex strTest = strTest + 2 and then using this in vlookup... varVlookupVarient = Application.VLookup(strAccountWeek, rngLookup, strTest, False) Thanks once again.... Veeraan "Jim Rech" wrote: strAccountWeek = ComboBox2.Value The "Value" of a combobox is what is displayed in it. But Vlookup needs an 'offset' number. So you should use the combo box's ListIndex property (+1) rather than its Value property. -- Jim "veeraan" wrote in message ... Hi, I need help.... I have a worksheet which looks like this... StoreNo--- 201 202 203 Week 1 X Week 2 y Week 3 z I have two combobox's on main sheet where users can select Week's and Store numbers. So say I can select Week 1 and Store 201. Once users have selected these two variables , they click on a button and the corresponding data should be populated in a textbox . So when I click the button , the output in textbox should be 'X' for above example. I have the following code in button click event: Dim strStoreNumber As String Dim strAccountWeek As String Dim rngLookup As Range Dim varVlookupVarient As Variant Private Sub CommandButton1_Click() strStoreNumber = ComboBox1.Value strAccountWeek = ComboBox2.Value Set rngLookup = Application.Workbooks("POS.xls").Worksheets("Sheet 1").Range("A:DW") ' this doesnt work.... varVlookupVarient = Application.VLookup(strAccountWeek, rngLookup, ComboBox1.Value, False) ' Below line works but I need the column in Vlookup to be dynamically selected..... 'varVlookupVarient = Application.VLookup(strAccountWeek, rngLookup, 2, False) MsgBox ("This is what you get" & varVlookupVarient) ' Once I get this data right, I can then have this as TextBox.value. End Sub Please help..... I am not sure if I am doing this right...is there another way instead of using Vlookups... Regards Veeraan |
| All times are GMT +1. The time now is 05:16 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com