VBA testing values in arrays-CodePudding

Im trying to write my first more extended macro in VBA and I'm having trouble with testing if a value in Array 1 is also in Array 2. Below my code with comments. I hope someone can help me with this as it's driving me crazy :-) Thank you in advance!

Sub PullingTrxData()

Dim TrxArray As Variant
Dim InvArray As Variant
Dim emailColumn As Range
Dim wsTrx As Worksheet
Dim wsInvoices As Worksheet
Dim trxRange As Range
Dim LastRow As Long
Dim i As Long
Dim j As Long

'setting sheets as variables
Set wsTrx = ThisWorkbook.Worksheets("Transactions")
Set wsInvoices = ThisWorkbook.Worksheets("Invoices Summary")

'finding last non empty row number in column c - email Invoices worksheet
LastRow = wsInvoices.Cells(wsInvoices.Rows.Count, "C").End(xlUp).Row

wsInvoices.Activate
'setting range of all emails already in invoices
If wsInvoices.Range("C3") <> "" Then
    Set emailColumn = wsInvoices.Range("C2", Range("C2").End(xlDown))
    Else: Set emailColumn = wsInvoices.Range("C2")
End If

'loading emails already on invoices sheet into an array
InvArray = emailColumn.Value

'setting range of all transactions  -why do I have to activate wsTrx for it to work?
wsTrx.Activate
Set trxRange = wsTrx.Range("A2", Range("A1").End(xlToRight).End(xlDown))

'loading transactions into array
TrxArray = trxRange.Value

'looping through array and checking if the email address from TransactionsList is already listed on Invoices Summary

For i = LBound(TrxArray, 1) To UBound(TrxArray, 1)

    For j = LBound(InvArray) To UBound(InvArray)
    'testing if email in TrxArray(i,1) already in InvArray(j) if yes then next else add to first empty cell in column C on Invoices summary sheet
        If TrxArray(i, 1) = InvArray(j) Then
        Next j
        Else: ThisWorkbook.Worksheets("Invoices Summary").Range("C" & LastRow).Offset(1, 0).Value = InvArray(j)
        End If
    Next j

Next i

End Sub

CodePudding user response：

Match Value of One Array in Another

Instead of two loops, you could use Application.Match with one loop:

Dim Trx1Array As Variant: Trx1Array = trxRange.Columns(1).Value

For i = 1 To UBound(InvArray, 1)
    If IsError(Application.Match(InvArray(i, 1), Trx1Array, 0)) Then ' not found
        ThisWorkbook.Worksheets("Invoices Summary").Range("C" & LastRow) _
            .Offset(1, 0).Value = InvArray(i)
    'Else ' found (in Trx1Array)
    End If
Next i