CODEWITHSUNDEEP
vbamultidimensional-array
BlackBear
BlackBear

Reputation: 385

Index match on 2D array inside VBA

This is not the usual case where the array is defined within Excel. It is defined within VBA, and I need to find the proper value given a value found in a cell in Excel. In cell A1 I have the value DE000C5RQDA9. In B1 I want to return RXM1.

The actual array is more of a (0 to 9, 0 to XX) so do not think a dictionary is feasible. XX changes dynamically.

Example

 Sub Test()

 Dim arr(0 To 1, 0 To 1)
 arr(0,0) = "RXM1"
 arr(0,1) = "UBM1"
 arr(1,0) = "DE000C5RQDA9"
 arr(1,1) = "DE000C5RQDD3"
 
With Application
   ActiveSheet.Cells(1, 2) = .Index(arr(0), .Match(ActiveSheet.Cells(1,1), arr(1), 0))
End with

 End sub

Upvotes: 0

Views: 593

Answers (1)

norie
norie

Reputation: 9857

You can use Application.Index to get the columns you want to use in the INDEX/MATCH.

The same idea could be used for rows.

Sub Test()
Dim arr(0 To 1, 0 To 1)
Dim col1 As Variant
Dim col2 As Variant

    arr(0, 0) = "RXM1"
    arr(0, 1) = "UBM1"
    arr(1, 0) = "DE000C5RQDA9"
    arr(1, 1) = "DE000C5RQDD3"

    col1 = Application.Index(arr, 1)
    col2 = Application.Index(arr, 2)
    With Application
        ActiveSheet.Cells(1, 2) = .Index(col1, .Match(ActiveSheet.Cells(1, 1), col2))
    End With

End Sub

Upvotes: 3

Related Questions