我有三個具有一致標題的作業表，但列數各不相同：
- 活動串列
- 當前串列
- 新名單
對于重復實體，我需要將“新串列”中的 A 列與“活動串列”中的 B 列進行比較。我只想加載從第 2 行開始的 A 列中的唯一實體，以及 B 列和 C 列中“活動串列”作業表最后填充行下方的“新串列”上的 B 列中的關聯單元格。
為此，我嘗試使用腳本字典，但在以下代碼行中我的物件范圍內收到運行時錯誤 1004：
Dict.Add Key:=NL.Range(i, "A").Value, Item:=vbNullString
這是我從StackOverflow 上的問題#55499372 中模仿的完整代碼：
Sub load_new()
Dim LastRow As Long
Dim i As Long
Dim Dict As Scripting.Dictionary
Set Dict = New Scripting.Dictionary
Dim CL As Worksheet
Set CL = ThisWorkbook.Worksheets("CURRENT LIST")
Dim NL As Worksheet
Set NL = ThisWorkbook.Worksheets("NEW LIST")
Dim AL As Worksheet
Set AL = ThisWorkbook.Worksheets("ACTIVE LIST")
'Retrieves the last row of column A
With NL
LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
End With
For i = 2 To LastRow
Dict.Add Key:=NL.Range(i, 1).Value, Item:=vbNullString
Next i
'Retrieves the last row of column B
With AL
LastRow = .Cells(.Rows.Count, 2).End(xlUp).Row
End With
For i = 2 To LastRow
If Not Dict.Exists(AL.Range(i, 2).Value) Then
End If
Next i
End Sub
uj5u.com熱心網友回復：
使用 Active List 列 B 中的鍵加載字典，然后掃描新串列列 A 檢查鍵是否不存在。
Option Explicit
Sub load_new()
Dim wsCL As Worksheet, wsNL As Worksheet, wsAL As Worksheet
Dim LastRowAL As Long, LastRowNL As Long
Dim i As Long, n As Long, key As String
Dim Dict As Scripting.dictionary
Set Dict = New Scripting.dictionary
With ThisWorkbook
'Set wsCL = .Sheets("CURRENT LIST")
Set wsNL = .Sheets("NEW LIST")
Set wsAL = .Sheets("ACTIVE LIST")
End With
' Active List
With wsAL
LastRowAL = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = 2 To LastRowAL
key = Trim(.Cells(i, "B"))
If Len(key) > 0 Then
Dict.Add key, i
End If
Next i
End With
' New List
With wsNL
LastRowNL = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To LastRowNL
key = Trim(.Cells(i, "A"))
If Not Dict.Exists(key) Then
LastRowAL = LastRowAL 1
wsAL.Cells(LastRowAL, "B") = key
wsAL.Cells(LastRowAL, "C") = .Cells(i, "B")
n = n 1
End If
Next i
End With
MsgBox n & " rows added to " & wsAL.Name
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/325987.html