Index/match macro

[edbdelen]

I have the following reference table:

-----1.2---1.4---1.6---1.8
XXX--24----32----14---42
YYY--13----14----15---12
ZZZ--18----16----12---12


Now I created a user form with Combobox1, Combobox2 and textbox1.

for combox1 rowsouce = left column and Combobox2 value from top values

fot textbox1 depends on the intersection of selected content of CB1 & CB2

example:

CB1 = XXX
CB2 = 1.6
TB1 should be automatic to display intersection value 14.

How to code this scenario? Please Help!

Thanks
Ed

 

[wholesaleguy]

Please take my advise carefully, I am a new member as well, but this looks like something I have worked on recently, and I found a lot of help with the following video.

Hope it helps!

http://www.youtube.com/user/ExcelIsFun#p/search/7/DedcDCkC5PA

 

[edbdelen]

thanks. but i don't have youtube access in our office.

ed

 

[Dryver14]

Is there a reason this can not go on the page as a straight index/match allowing user to input the x and y values?


Edit: new thought.

Put the index match formula out of the way ie zza

then input box one populates x
input box two polulates y

text box 1 displayes value of those 2 ie cell reference zzc

 

[edbdelen]

Hi Dryver14,

Thanks for your reply, yes I can do it directly on the sheet using index/match. The reason I want to use it with macro is that it's only a part of my userform more CB and TB are on the form. Computations are done in form as well then save the data into hidden sheet as database. ONly this part, I need solution to complete my project.

thanks.
ed

 

[wholesaleguy]

Ok, Try this:

select cells containing 1.2,1.4,1.6,1.8 and name this range of cells DATA1
select cells containing xxx,yyy,zzz and name this range of cells DATA2
in cell A6 or anywhere you want it to be, set up a data validation using a list and select =DATA2 as your range of information.
in cell A7 or anywhere you want it to be, set up a data validation using a list and select =DATA1 as your range of information.

this will allow your user to pick the information needed.

in A8 use =index(($B2:$E4),match($A$6,DATA2,0),match($A$7,DATA1,0))
This should allow your user to select the information from drop down lists, and then output the information listed at the intersection.

Hope it helps. It worked in my test.

 

[wholesaleguy]

Sorry,
I missed the part where you wanted to do this as a macro. Please ignore my rantings.

 

[Domenic]

Assuming that A1:E4 contains the table, maybe something like this...

[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] CommandButton1_Click()

    [color=darkblue]Dim[/color] MatchVal1 [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    [color=darkblue]Dim[/color] MatchVal2 [color=darkblue]As[/color] [color=darkblue]Variant[/color]

    MatchVal1 = Application.Match(Me.ComboBox1.Value, Range("A2:A4"), 0)
    
    [color=darkblue]If[/color] IsError(MatchVal1) [color=darkblue]Then[/color]
        MsgBox "ComboBox1 value not found...", vbExclamation
        [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
    MatchVal2 = Application.Match(CDbl(Me.ComboBox2.Value), Range("B1:E1"), 0)
    
    [color=darkblue]If[/color] IsError(MatchVal2) [color=darkblue]Then[/color]
        MsgBox "ComboBox2 value not found...", vbExclamation
        [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    [color=darkblue]End[/color] [color=darkblue]If[/color]
        
    Me.TextBox1.Value = Application.Index(Range("B2:E4"), MatchVal1, MatchVal2)

[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

Change the control names, accordingly.

 

[edbdelen]

Thanks Dominic. Your code exactly what I need. It works great.

Regards,
Ed