| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Result in Array
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Lookup Result in Array
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 -- Regards, Tom Ogilvy "ExcelMonkey" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Lookup Result in Array
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. -----Original Message----- 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 -- Regards, Tom Ogilvy "ExcelMonkey" wrote in message ... 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 . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Lookup Result in Array
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. -- Regards, Tom Ogilvy "ExcelMonkey" wrote in message ... 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. -----Original Message----- 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 -- Regards, Tom Ogilvy "ExcelMonkey" wrote in message ... 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 . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| lookup help. lookup result based on data in 2 columns | Excel Worksheet Functions | |||
| Use array for lookup value, to return array of lookups | Excel Discussion (Misc queries) | |||
| Please help on array search & result | Excel Worksheet Functions | |||
| how to use linest to get the result as ARRAY? | Excel Worksheet Functions | |||
| Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
![ExcelMonkey[_190_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
