CODEWITHSUNDEEP
excelvba
user7911549
user7911549

Reputation: 31

VBA Finding Values in one Array Using Another Array

I have 2 arrays.

  1. Array1 is a list that includes 3 columns.
  2. Array2 is a list that contains 2 columns.
  3. Columns 1 and 2 of both arrays have the same information.

I need to figure out how to create a third array that contains the 3 columns in Array1 and only contains items that belong to Array2.

Any help is much appreciated.

Upvotes: 3

Views: 1292

Answers (1)

brettdj
brettdj

Reputation: 55702

Something like this:

Assumes your arrays are in A1:C10 and E1:F10 , pls change to suit.

Sub Arid()
Dim X
Dim Y
Dim Z

Dim lngCnt As Long
Dim lngCnt2 As Long
Dim lngCnt3 As Long

X = Range([a1], [c10]).Value2
Y = Range([E1], [F10]).Value2

ReDim Z(1 To UBound(X), 1 To 3)

For lngCnt = 1 To UBound(X, 1)
    For lngCnt2 = 1 To UBound(Y, 1)
        If X(lngCnt, 1) = Y(lngCnt2, 1) Then
            If X(lngCnt, 2) = Y(lngCnt2, 2) Then
                lngCnt3 = lngCnt3 + 1
                Z(lngCnt3, 1) = X(lngCnt, 1)
                Z(lngCnt3, 2) = X(lngCnt, 2)
                Z(lngCnt3, 3) = X(lngCnt, 3)
                Exit For
            End If
        End If
    Next
Next

End Sub

Upvotes: 1

Related Questions