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

Office IT屋
问 题

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

Here's the columnA in sheet1

enter image description here

Here's the column A in sheet2 enter image description here

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

enter image description here

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 enter image description here

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

enter image description here

Thanks for the help in advance.

Here's what I got so far

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
解决方案

You can search a range for a value using Range.Find

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.

Here is an example:

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

本文地址:IT屋 » Compare 2 cells in different sheets in VBA(Excel 2010)

问 题

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



这里是sheet1中的列A





这里是sheet2中的列A



这是我需要作为sheet1的输出





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



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





感谢您提前的帮助。



这是我到目前为止



  Sub Button1_Click()
错误GoTo ErrorHndler:
Dim myRange As Range
Dim sRng As Range

设置myRange = Range(“A1:A50 “)

开始:
对于每个sRng在myRange
如果sRng Like Sheets(”Sheet2“)。Range(”A1“)。Value Then
MsgBox表格(“Sheet2”)。范围(“A1”)。值)<----它不通过这里
(----我没有想法什么放在这里-----)
'GoTo NextCell
Else
'GoTo开始
MsgBox(Sheets(“Sheet2”)。Range(“A1”)。Value)
'MsgBox“Doesn' t匹配“< -----用于调试目的
End If
NextCell:
下一步sRng

ErrorHandler:
MsgBox”“
End Sub

解决方案

您可以使用 Range.Find



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



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



这是一个例子:



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

'将查找值从sheet1复制到sheet3
表格(“Sheet1”)。选择
TotalRows = ActiveSheet.UsedRange.Rows.Count
范围(“A1:A”& TotalRows).Copy Destination:= Sheets(“Sheet3”)。Range(“A1”)

'转到目的地单
表格(“Sheet3”)。选择

对于i = 1 To TotalRows
'搜索sheet2上的值
设置rng = Sheets(“Sheet2”)。UsedRange.Find(Cells(i,1).Value)
'如果找到它的价值在目标表
如果不是rng不是然后
单元格(i,2).Value = rng.Value
结束如果
Next
End Sub

本文地址:IT屋 » 在VBA中比较不同工作表中的2个单元格(Excel 2010)