Option Explicit
Sub main()
Dim sh1 As Worksheet: Set sh1 = Worksheets("Sheet")
Dim sh2 As Worksheet: Set sh2 = Worksheets("Sheet2")
Dim cell As Range
Dim nFounds As Long
With CreateObject("Scripting.Dictionary")
For Each cell In sh1.Range("A1", sh1.Cells(sh1.Rows.Count, 1).End(xlUp))
.Item(cell.Value) = cell.Offset(, 1)
Next
If .Count > 0 Then
ReDim founds(1 To .Count)
For Each cell In sh2.Range("A1", sh2.Cells(sh2.Rows.Count, 1).End(xlUp))
If .exists(cell.Value) Then
If .Item(cell.Value) <> cell.Offset(, 1) Then
nFounds = nFounds + 1
founds(nFounds) = cell.Value
End If
End If
Next
If nFounds > 0 Then
ReDim Preserve founds(1 To nFounds)
MsgBox "common factors with different values: " & vbCrLf & vbCrLf & Join(founds, vbCrLf)
Else
MsgBox "no common factors found"
End If
End If
End With
End Sub
2 回答
您可以简单地使用VLOOKUP来执行此功能 . 在工作表2上,突出显示所有数据,转到公式>名称管理器>新建,并为其命名,例如数据 .
然后在Sheet 1中使用Col3:
然后,如果Sheet 1 Col1 Row1中的值存在于Sheet 2 Col1中,它将在Sheet 1 Col3中打印相应的值 . 如果它不存在,则将其留空 . 只需拖动公式即可完成工作表1中的所有单元格 .
使用上面的公式和您的信息,如果A在Sheet 1 Cell A1中,Sheet 1 Cell A3将读为0,这是Sheet 2中A的对应值.Bell 3和C3将是空白的,因为它们不存在于表2 .
你可以用它