VLookup not working in VBA

[DonEB]

Hello,

I'm attempting to do a VLookup in a macro while using the Match function to dynamically determine which column to look in.

I did some testing by sending values to a Msgbox to validate I am passing the correct values.
Ex: MsgBox "Match Value is : " & Match1 <== the value was 13
MsgBox "Lookup Value is : " & Sheet2.Range("A2").Value <== the value was 8

In both both of the above examples, the value that I expected to be passed were. However, the value I am expected to be "Found" using the VLoopup was not correct.

Match1 = WorksheetFunction.Match(Sheet2.Range("B2").Value, Range("N1:U1"), 0) + 3
FoundValue = Application.WorksheetFunction.VLookup(Sheet2.Range("A2").Value, Sheet2.Range("K3:U10"), Match1, False)

Note: Expected results is ==> 0.44444

I'm actually coming back with the number "0".

Section of Table Used to Lookup:
NOTES: (1) the real table does extend beyond to column U and down to row 10
(2) the ONLY difference is that I changed the names. All numerical values are the same.

	K	L	M	N	O
1				8	13
2				Dent	Bulls
3	3	Doe	.75	.33333	.33333
4	4	Zully	.66	0	0
5	5	Hill	.75	.33333	.33333
6	7	Smith	.66	.25	.25
7	8	Dent	.01		.44444
8	9	Vails	.8	.25	.25

<tbody>
</tbody>

Can anyone see what I'm doing wrong? Your help would be appreciated.

Thank you.

 

[DanteAmor]

Did you read post #7 ? with a single line you can find the value.

Do you want all the A-B, A-C, A-D, B-C, B-D and C-D combinations, or only the A-B and C-D combinations?

You want the result of all the combinations in an arrangement or better yet, in Excel cells. Or you just want a result.
Why not better raise everything you have, what you want to do and what you want as a result.
The above is because you started with a simple search that was solved because you were not correctly declaring a variable, but now we are going in a loop of combinations, but you still do not ask what your final objective is.

 

[offthelip]

Your problem is you defined myarray as type long, msgbox only types out strings, so the assignment to myarray is perfectly ok ( I think) I think the problem is just with your method of looking at it.
try this:

dim tt as string


tt = cstr(myarray(X,y)
MsgBox ("This is my array value: " & tt)

or faster try looking at myarray on the locals window.

 

[offthelip]

I finally got back to my computer and solved your problem you need to change the declaration of myarray
to:

Dim MyArray(1 To 5, 1 To 5) As Variant