在VBA中比较不同工作表中的2个单元格（Excel 2010） [英] Compare 2 cells in different sheets in VBA(Excel 2010)

问题描述

我可以要求一个示例宏代码来比较两张不同的表格中的2个不同的列。

Hi Can I ask for a sample macro code to compare 2 different columns from 2 different sheets.

这里是sheet1中的列A

Here's the columnA in sheet1

这里是sheet2中的列A

Here's the column A in sheet2

这是我需要作为sheet1的输出

Here's what I need to make as an output in sheet1

然后，上述图片中的列A sheet1中没有匹配的所有单元格都应该被剪切并复制column C in sheet1 like below below

Then all cells in column A sheet1 without match such as red in the picture above should be cut and copied in column C in sheet1 like the below

最后没有匹配的列A表2中的所有单元格都应该被剪切并粘贴在表1中的列D中，如ABC：PINK，ABC：VIOLET和ABC：BLACK，如下所示

lastly all cells in column A sheet 2 that has no match should be cut as well and pasted in column D in sheet 1 such as ABC:PINK, ABC:VIOLET and ABC:BLACK as shown below

感谢您提前的帮助。

这是我到目前为止

Sub Button1_Click()
On Error GoTo ErrorHndler:
Dim myRange As Range
Dim sRng As Range

Set myRange = Range("A1:A50")

Start:
     For Each sRng In myRange
       If sRng Like Sheets("Sheet2").Range("A1").Value Then
          MsgBox (Sheets("Sheet2").Range("A1").Value) <----it does not pass here 
          (----I have no Idea what to put here-----)
          'GoTo NextCell
       Else
          'GoTo Start
          MsgBox (Sheets("Sheet2").Range("A1").Value)
          'MsgBox "Doesn't match"  <-----for debugging purposes
       End If
 NextCell:
 Next sRng

 ErrorHandler:
 MsgBox ""
 End Sub

推荐答案

您可以使用 Range.Find

Range.Find 返回 Nothing if如果找到匹配，则找不到匹配项或范围。

Range.Find returns Nothing if no match is found or a Range if a match is found.

您可以检查两个对象是否使用

You can check if two objects refer to the same object using the is operator.

这是一个例子：

Sub lookup()
    Dim TotalRows As Long
    Dim rng As Range
    Dim i As Long

    'Copy lookup values from sheet1 to sheet3
    Sheets("Sheet1").Select
    TotalRows = ActiveSheet.UsedRange.Rows.Count
    Range("A1:A" & TotalRows).Copy Destination:=Sheets("Sheet3").Range("A1")

    'Go to the destination sheet
    Sheets("Sheet3").Select

    For i = 1 To TotalRows
        'Search for the value on sheet2
        Set rng = Sheets("Sheet2").UsedRange.Find(Cells(i, 1).Value)
        'If it is found put its value on the destination sheet
        If Not rng Is Nothing Then
            Cells(i, 2).Value = rng.Value
        End If
    Next
End Sub

这篇关于在VBA中比较不同工作表中的2个单元格（Excel 2010）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！