比较两个不同工作表中的两列

Question

我需要比较不同工作表中的两列。

首张(500 行(

列 A(编号( 列 B(字符串

( 列 C(编号(列 D(编号(列 E(编号(列 F(编号(第二张

列 A(编号( 列 B(字符串( 列 C(编号(列 D(编号(列 E(编号(没有列 F

我想在工作表 3 中打印出第二张纸中不存在的 SITESID(列a(。

Option Explicit
Sub Compare()
Dim Row1Crnt As Long
Dim Row2Crnt As Long
Dim Row3Crnt As Long    
Dim Row1Last As Long
Dim Row2Last As Long    
Dim ValueSheet1
Dim ValueSheet2
Dim duplicate As Boolean    
Dim maxColmn As Long
Dim i
maxColmn = 10  ' number of column to compare
For i = 1 To maxColmn
With Sheets("Sheet1")
Row1Last = .Cells(Rows.Count, i).End(xlUp).Row
End With
With Sheets("Sheet2")
Row2Last = .Cells(Rows.Count, i).End(xlUp).Row
End With
Row1Crnt = 2
Row2Crnt = 2
Row3Crnt = 2    
maxColmn = 10
Do While Row2Crnt <= Row2Last
duplicate = False
Row1Crnt = 2
With Sheets("Sheet2")
ValueSheet2 = .Cells(Row2Crnt, i).Value
End With
Do While Row1Crnt <= Row1Last
With Sheets("Sheet1")
ValueSheet1 = .Cells(Row1Crnt, i).Value
End With
If ValueSheet1 = ValueSheet2 Then
duplicate = True
Exit Do
End If
Row1Crnt = Row1Crnt + 1
Loop
If duplicate = False Then
With Sheets("Sheet3")
.Cells(Row3Crnt, i).Value = ValueSheet2
Row3Crnt = Row3Crnt + 1
End With
End If
Row2Crnt = Row2Crnt + 1
Loop
Next
End Sub

但我会把它当作结果 第二个工作表中不存在的所有列 A (SITESID( 工作表 2 和列 B(名称(

Answer 1

最快的"检查存在"是application.match。

sub compare
dim a as long, arr as variant, chk as variant
with worksheets("sheet1")
arr = .range(.cells(2, "A"), .cells(.rows.count, "A").end(xlup)).value2
end with
with worksheets("sheet3")
for a = lbound(arr, 1) to ubound(arr, 1)
if iserror(application.match(arr(a, 1), worksheets("sheet2").columns("A"), 0)) then
.cells(.rows.count, "A").end(xlup).offset(1, 0) = arr(a, 1)
end if
next a
end with
end sub