Vlookup - Issue....

[veeraan]

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("Sheet1").Range("AW")

' 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

 

[sebastienm]

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>

 

[Jim Rech]

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.

 

[veeraan]

Thanks Sebastienm.....

It works and is much simpler...

 

[veeraan]

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