我有兩列包含字母數字資料 - 作業簿 1 中的 A 列和作業簿 2 中的 A 列。
workbook1 中的 A 列包含 40,000 行（包含重復值，但它們是必需的），workbook2 中的 A 列包含 25,000（包含唯一值）行。我必須搜索 B 列中是否存在 A 列值。如果是，我必須將 workbook1 中的 B 列更新為 user_found。
我嘗試過回圈，但由于資料量很大，Excel 經常崩潰并且需要很多時間。請幫助我是初學者。
Dim arr As Variant
With AAws1
arr = AAws1.Range("A4:A" & Range("A" & Rows.Count).End(xlUp).Row).Value
End With
Dim varr As Variant
With userws1
varr = userws1.Range("A4:A" & Range("A" & Rows.Count).End(xlUp).Row).Value
End With
m = 4
Dim x, y, match As Boolean
For Each x In arr
match = False
For Each y In varr
If x = y Then
match = True
AAws1.Cells(m, 4).Value = "user found"
End If
' m = m 1
Next y
If Not match Then
AAws1.Cells(m, 4).Value = "Not found"
End If
m = m 1
Next
uj5u.com熱心網友回復：
標記找到的值
- 這將創建對
srg包含唯一值的 Source Lookup Column Range ( )的參考，并將此范圍用作 in 的第二個引數，Application.Match因為它在范圍上的效率是在陣列上的數倍。目標查找列范圍 (drg) 中的值將寫入陣列 (dData)，該陣列也將用作結果陣列 (dData)。在將結果寫入陣列 (dData) 后，其值將被復制到給定的列 (dCol)，即目標范圍 (drg.EntireRow.Columns(dCol))。
Option Explicit
Sub FlagUnique()
Const dCol As String = "B"
Const dFlag As String = "user found"
' Source - unique
Dim srg As Range
With userws1
' 'Application.Match' is multiple times faster on a range
' than on an array.
Set srg = .Range("A4", .Range("A" & .Rows.Count).End(xlUp))
End With
' Destination - duplicate
Dim drg As Range
Dim dData As Variant
With AAws1
Set drg = .Range("A4", .Range("A" & .Rows.Count).End(xlUp))
dData = drg.Value
End With
Dim dValue As Variant
Dim sIndex As Variant
Dim r As Long
Dim IsFound As Boolean
For r = 1 To UBound(dData)
dValue = dData(r, 1)
If Not IsError(dValue) Then
If Len(dValue) > 0 Then
sIndex = Application.Match(dValue, srg, 0)
If IsNumeric(sIndex) Then
dData(r, 1) = dFlag
IsFound = True
End If
End If
End If
If IsFound Then
IsFound = False
Else
dData(r, 1) = "not found" ' I would prefer 'dData(r, 1) = Empty'
End If
Next r
drg.EntireRow.Columns(dCol).Value = dData
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/366316.html