Lookup Result in Array

[ExcelMonkey]

I am trying to return a value for "A" in my select case
below. Its a value extracted from an array using the
equivalent of an Index formual with a Match in side it.
however I am not sure how to use it on a 2D array where I
want to focus on specific columns.

I have an 2D array of Data that looks like this:

1 1
2 0
3 0
4 2
5 0
6 3

I want to be able to extract the values in column 1 based
on row identified by the values in column 2. The rows in
column 2 are set out below in my For Loop (X = 1 to 3).
I pass X to a second sub and within that sub I need to
return the row position of X in the array (column 2).

When X = 1, it is found in row 1 of column 2
When X = 2, it is found in row 4 of column 2
When X = 3, it is found in row 6 of column 2

I then use this row number in an index formula on column
1. Hence Index(column1 of array, row, column).
Effectively I am creating an index of column 1 using the
row value returned from a Match of X on column 2.


The entire routine that looks like this. The value I am
trying to calc is in the Select Case in Sub2

Sub1 ()
For Each sht in Workbook
For X = 1 to 3
Call Sub2(X)
Next
Next

Sub2 (A As Integer)

Select Case A
I don't know the proper syntax for calculating A!!!!!
A = Application.Index(Arraycol1,.Match(A,Arraycol2,0),1)

Case Is = 1
Code
Case Is = 2
Code
Case Is = 3
Code
Case Is = 4
Code
Case Is = 5
Code
Case Is = 6
Code
End Select

End Sub

Can anyone one guide me on this? Sorry for the maximum
detail. I tried to simplify it.

Thanks

 

[Tom Ogilvy]

Public vArray As Variant
Sub Sub1()
'ReDim vArray(0 To 5, 0 To 1)
Dim sht As Worksheet
Dim x As Integer
vArray = Evaluate("{1 ,1;2, 0;3, 0;" & _
" 4, 2; 5, 0; 6, 3}")

'For Each sht In ThisWorkbook.Worksheets
For x = 1 To 3
Call Sub2(x)
Next
'Next

End Sub


Sub Sub2(A As Integer)


B = Application.Match(A, Application.Index(vArray, 0, 2), 0)
C = Application.Index(vArray, B, 1)

Select Case C
Case Is = 1
Debug.Print 1
Case Is = 2
Debug.Print 2
Case Is = 3
Debug.Print 3
Case Is = 4
Debug.Print 4
Case Is = 5
Debug.Print 5
Case Is = 6
Debug.Print 6
End Select

End Sub



returns
1
4
6

 

[ExcelMonkey]

Wow. I was worried that I wasn't being clear. Need some
time to look at this. Have never used teh evaluate
function before. Sincere thank-you Tom.

 

[Tom Ogilvy]

I just used evaluate to build the array you posted. You don't need to use
it as you are generating it from some other part of your code. I just
needed it for testing.