由于某种原因我的代码无法正常工作，我已经使用这种类型的代码一千次了，无论出于何种原因它都不匹配。当我意识到140,000条记录时，关于如何更改或改善此问题的任何建议都很多！
Dim name1(140000) As String, name2(140000) As String, answer(140000) As String
For i = 1 To 140000
name1(i) = ActiveWorkbook.Worksheets("Sheet0").Cells(i, 1).value
name2(i) = ActiveWorkbook.Worksheets("Sheet1").Cells(i, 6).value
answer(i) = ActiveWorkbook.Worksheets("Sheet0").Cells(i, 13).value
If name1(i) = name2(i) Then
answer(i) = "yes"
End If
Next
嗨，谢谢你，这是问题，尽管值正在更改，所以工作表1中的名称可能在“ A1”中，但是工作表2中的名称在“ F12”中，然后下周可能在“ F14”中，因此一种使用代码进行相应更新的方法，还使用了您的vba，但仍然没有运气:( – 9分钟前，Calum
公式是正确的方法。您可以COUNTIF用来检查是否存在。将此公式放在单元格M1中，然后将其下拉。
=IF(COUNTIF($F$1:$F$14000,A1)>0,"Yes","No")
但是，如果您仍然想使用代码，请尝试以下操作（未经测试）
Sub Sample()
Dim name1 As Variant, name2 As Variant, answer(1 To 14000) As String
Dim ws As Worksheet
Dim i As Long
With ThisWorkbook
name1 = .Worksheets("Sheet0").Range("A1:A14000").Value
name2 = .Worksheets("Sheet1").Range("F1:F14000").Value
For i = 1 To 14000
If IsInArray(name1(i, 1), name2) Then answer(i) = "Yes" Else answer(i) = "No"
Next i
.Worksheets("Sheet1").Range("M1").Resize(UBound(answer), 1).Value = _
Application.WorksheetFunction.Transpose(answer)
End With
End Sub
Function IsInArray(stringToBeFound As Variant, arr As Variant) As Boolean
Dim bDimen As Byte, i As Long
On Error Resume Next
If IsError(UBound(arr, 2)) Then bDimen = 1 Else bDimen = 2
On Error GoTo 0
Select Case bDimen
Case 1
On Error Resume Next
IsInArray = Application.Match(stringToBeFound, arr, 0)
On Error GoTo 0
Case 2
For i = 1 To UBound(arr, 2)
On Error Resume Next
IsInArray = Application.Match(stringToBeFound, Application.Index(arr, , i), 0)
On Error GoTo 0
If IsInArray = True Then Exit For
Next
End Select
End Function
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句