如何将列的文本与不同工作簿的列中的文本进行比较?

Question

我想检查一个工作簿中 A 列的内容是否等于另一个工作簿中 A 列的内容。

逐个单元格它们应该是相同的。

我能找到的最好的是：

Sub RowCompare()
Dim ary1() As Variant
Dim Range1 As Range, Range2 As Range, rr1 As Range, rr2 As Range
Set Range1 = Range("B9:F20")
Set Range2 = Range("I16:M27")
Set rr1 = Range1.Rows(5)
Set rr2 = Range2.Rows(5)
ary1 = Application.Transpose(Application.Transpose(rr1))
ary2 = Application.Transpose(Application.Transpose(rr2))
st1 = Join(ary1, ",")
st2 = Join(ary2, ",")
If st1 = st2 Then
MsgBox "the same"
Else
MsgBox "different"
End If
End Sub

Answer 1

在设置Range1和Range2时，您可能希望在Range之前指定Workbook和Worksheet

例如，进行以下编辑：

Set Range1 = Workbooks("workbook 1 name").Worksheets("your sheet name").Range("B9:F20")
Set Range2 = Workbooks("workbook 2 name").Worksheets("other sheet name").Range("I16:M27")

另外，我看到您从最快的方法中抓取了这个，以检查excel vba中的两个范围是否相等

Answer 2

Sub CompareColumns()
Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("Sheet1")
Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Sheets("Sheet2")
Dim Arr1 As Variant, Arr2 As Variant
Dim i As Long, Same As Boolean
'Assume they are the same before starting loop
Same = True
'Assign some range to Array
Arr1 = ws1.Range("A1:A20").Value
Arr2 = ws2.Range("A1:A20").Value
'Loop through Array
For i = LBound(Arr1) To UBound(Arr2)
If Arr1(i, 1) <> Arr2(i, 1) Then
Same = False
End If
Next i
'Check the value of "Same"
If Same = False Then MsgBox "Ranges are not equal!"

End Sub