Is there any direct API to find the matching items for the same index in a variant of the order var(a,b)?

Question

I've got a variant let's say var(9,175) (obtained using ADO object for reading excel) where 9 represents the columns and 175 represents the rows, filled with some data.

1. How can I retrieve entire row data (for each column value the row number will be same, obvious) based on 3 columns (for the same row) taken from other workbook. So, the matching keys will be these 3 column values from other workbook.
2. I don't want to perform the looping operation, as it'll be time-consuming.

Example: In variant var I've got some data at 2nd row as:

#125    a    [68]    :1    [197]    :2    1    1645    123

From other workbook, I took the data as:

[68]    :1    [197]

Based on these 3 values, How can I directly get the data of the 2nd row entirely based on some Find or Match commands?

Answer 1

It's not clear to me what you're trying to do exactly, but if you know the row-number in the array that you want the entire row from, then you can use the Worksheet.Index function.

Here's an example using a dummy 2D array:

Sub test()

  'Assign a 2D array
  Dim my2DArray() As Variant
  my2DArray() = [{1,2,3;4,5,6;7,8,9}]

  Const DESIRED_ROW As Long = 2

  'Use the Worksheet.Index function to extract an individual row
  Dim myRow() As Variant
  myRow = Application.WorksheetFunction.Index(my2DArray, DESIRED_ROW, 0)

  Debug.Print Join(myRow, ",") 'Prints 4,5,6

End Sub