Thread: Compare columns
View Single Post
  #7   Report Post  
Posted to microsoft.public.excel.programming
arjen van... arjen van... is offline
external usenet poster
 
Posts: 32
Default Compare columns

I'm not sure if this might be what you're looking for, but you can also
consider matching two arrays with each array being based on your two
spreadsheet ranges. Compare each element of the first array to each element
of the second array, and if it matches put the value in the spreadsheet.

Option Explicit

Sub MatchArrays()

Dim arrExposed, arrTransactions As Variant

With Sheets("Exposed")
arrExposed = .Range(.Range("A1"), .Range("A1").End(xlDown)).Value
End With

With Sheets("Transactions")
arrTransactions = .Range(.Range("A1"), .Range("A1").End(xlDown)).Value
End With

Dim i, j As Long

For i = LBound(arrExposed, 1) To UBound(arrExposed, 1)
For j = LBound(arrTransactions, 1) To UBound(arrTransactions, 1)
If arrExposed(i, 1) = arrTransactions(j, 1) Then
Debug.Print (arrExposed(i, 1))
Sheets("Exposed").Cells(i, 2).Value = arrExposed(i, 1)
End If
Next
Next

End Sub