VBA - Match a value in 2D array with a starting index

[Legacy 143009]

Hi,
I want to match a value in the second dimension of my array but starting from index 10. Is it possible? Something like:

With Application
        Debug.Print .Match("searchString", Range(.Index(myArr, 10, 2), .Index(myArr, UBound(myArr), 2)), 0)
End With

 

[Domenic]

It's unclear to me. Is it that you want to search the 10th row in your array? If so, try...

With Application
        Debug.Print .Match("searchString", .Index(myArr, 10, 0), 0)
End With

Hope this helps!

 

[Legacy 143009]

It's unclear to me. Is it that you want to search the 10th row in your array? If so, try...

With Application
        Debug.Print .Match("searchString", .Index(myArr, 10, 0), 0)
End With

Hope this helps!

To be more precise, I want to search in the second column of my array from 10th row to last row.

 

[Domenic]

Try the following...

Option Explicit

Sub test()

    Dim myArr As Variant
    myArr = Range("A1:C100").Value
    
    Dim mySlice As Variant
    mySlice = getColumnSliceFromArray(myArr, 10, UBound(myArr, 1), 2)
    
    Debug.Print Application.Match("z", mySlice, 0)
    
End Sub

Function getColumnSliceFromArray(ByRef arr As Variant, ByVal firstRow As Long, ByVal lastRow As Long, ByVal columnIndex As Long) As Variant
    getColumnSliceFromArray = Application.Index(arr, Evaluate("ROW(" & firstRow & ":" & lastRow & ")"), columnIndex)
End Function

Note that getColumnSliceFromArray() returns a one-column, two dimensional array. To return a one dimensional array, you can transpose the references returned by ROW in the Evaluate method...

Function getColumnSliceFromArray(ByRef arr As Variant, ByVal firstRow As Long, ByVal lastRow As Long, ByVal columnIndex As Long) As Variant
    getColumnSliceFromArray = Application.Index(arr, Evaluate("TRANSPOSE(ROW(" & firstRow & ":" & lastRow & "))"), columnIndex)
End Function

Hope this helps!

 

[Legacy 143009]

Note that getColumnSliceFromArray() returns a one-column, two dimensional array. To return a one dimensional array, you can transpose the references returned by ROW in the Evaluate method...

Very clear. Thanks a lot

 

[Domenic]

You're very welcome, cheers!