我試圖在 VBA 中撰寫我的第一個更擴展的宏，但在測驗陣列 1 中的值是否也在陣列 2 中時遇到問題。在我的代碼下方帶有注釋。我希望有人能幫我解決這個問題，因為它讓我發瘋:-) 提前謝謝你！
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
uj5u.com熱心網友回復：
匹配另一個陣列中的值
您可以使用Application.Match一個回圈，而不是兩個回圈：
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/368737.html