Finding value in matrix

[Sunshine]

Hi everybody!
I have a matrix in excel and I want the user to get an inputbox where they can type two different names that should specify the range in the matrix. Ex. John and Oranges looks up in my matrix how many oranges John bought and shows this number in a messagebox, Somebody who can help me with the code?

[StevenVB]

Hi Sunshine,


See if the attached excel is what you want.

[Sunshine]

Hi! It looks exactly like what I need, Thanks a lot! But how do I write the code if I want the button not on the same sheet as the matrix? What do I write instead of ActiveSheet?

[StevenVB]

If you dont want to see the button, you can delete it. Upon deleting, there are several others ways to activate the macro codes:
1.) Alt + F8, then run the codes; or
2.) Create your own button in another sheets, and link the button to the macro written; or
3.) Set a short-cut key (e.g. Ctrl+e) for that macro code


To designate the code not towards ActiveSheet, you can also specifically choose a worksheet's name:
To do so, change all "ActiveSheet" into "Sheets("Macro")"


For your convenience, the whole codes are shown as below:







Sub ToMatch()

Dim i, j, k As Integer
Dim RowNo, ColNo As Integer
Dim a, b, c As String

RowNo = 0
ColNo = 0
a = InputBox("Please input a name (e.g. John)", "Name", "John")
b = InputBox("Please input an item (e.g. Apple)", "Item", "Apple")

For i = 1 To Sheets("Macro").Cells(Rows.Count, 1).End(xlUp).Row
If Cells(i, 1).Value = a Then
RowNo = i
i = Sheets("Macro").Cells(Rows.Count, 1).End(xlUp).Row
End If
Next i

For j = 1 To Sheets("Macro").Cells(1, Columns.Count).End(xlToLeft).Column
If Cells(1, j).Value = b Then
ColNo = j
j = Sheets("Macro").Cells(1, Columns.Count).End(xlToLeft).Column
End If
Next j

If RowNo <> 0 And ColNo <> 0 Then
MsgBox ("The value is " & Cells(RowNo, ColNo).Value)
End If

End Sub

[Sunshine]

It works really good when the button is in the same sheet as the matrix, but as I move the button to another sheet and do as you told me to (changing all "ActiveSheet" into "Sheets("Mysheet") It doesn't work any more...

[StevenVB]

oh sorry, I think I forget to add Sheets("Macro") before every Cells

I've attached a new one which should be able to solve your problem.