来自 A 列的 VBA 检查值存在于另一个工作簿中

VBA check value from column A exists in another workbook

提问by user1810449

I am trying to build a macro that loops through a range of values within colA and check if they exist with another workbook. In one of them I would like to mark it "Worked"/"Not Worked"

我正在尝试构建一个循环遍历 colA 中的一系列值的宏，并检查它们是否存在于另一个工作簿中。在其中之一中，我想将其标记为“有效”/“无效”

Any guidance on where to start?

关于从哪里开始的任何指导？

采纳答案by Lopsided

Example

例子

Here is an example of what you're looking for. Remember that both the workbooks have to be opened in the same instance of Excel.

这是您正在寻找的示例。请记住，必须在同一个 Excel 实例中打开两个工作簿。

Sub check()

Dim i As Integer, k As Integer, j As Integer 'Define your counting variables
Dim Report1 As Worksheet, bReport As Workbook, Report2 As Worksheet, bReport2 As Workbook 'Define your workbook/worksheet variables

Set Report1 = Excel.ActiveSheet 'Assign active worksheet to report1
Set bReport = Report1.Parent 'Assign the workbook of report 1 to breport


On Error GoTo wbNotOpen 'If an error occurs while accessing the named workbook, send to the "wbNotOpen" line.
Set bReport2 = Excel.Workbooks("otherworkbookname.xlsm") 'Assign the other workbook which you are cross-referencing to the bReport2 variable.
Set Report2 = bReport2.Worksheets("otherworksheetname") 'Do the same with the worksheet.
On Error GoTo 0 'Reset the error handler (to undo the wbNotOpen line.)

k = Report1.UsedRange.Rows.Count 'Get the last used row of the first worksheet.
j = Report2.UsedRange.Rows.Count 'Get the last used row of the second worksheet.

For i = 2 To k 'Loop through the used rows of the first worksheet. I started at "2" to omit the header.
    'Next, I used the worksheet function "countIf" to quickly check if the value exists in the given range. This way we don't have to loop through the second worksheet each time.
    If Application.WorksheetFunction.CountIf(Report2.Range(Report2.Cells(2, 1), Report2.Cells(j, 1)), Report1.Cells(i, 1).Value) > 0 Then
        Report1.Cells(i, 5).Value = "Worked" 'If the value was found, enter "Worked" into column 5.
    Else
        Report1.Cells(i, 5).Value = "Not worked" 'If the value wasn't found, enter "Not worked" into column 5.
    End If
Next i




Exit Sub
'This is triggered in the event of an error while access the "other workbook".
wbNotOpen:
MsgBox ("Workbook not open. Please open all workbooks then try again.")
Exit Sub

End Sub

---

---

This link also includes steps that tell how to check if a cell exists in another workbook. The comments are useful.

此链接还包含说明如何检查单元格是否存在于另一个工作簿中的步骤。评论很有​​用。

• Excel macro - paste only non empty cells from one sheet to another (Stack Overflow)

• Excel 宏 - 仅将非空单元格从一张纸粘贴到另一张纸上（码）

回答by user1810449

Thanks to #Lopsided's solution, I have tweeked his code to bring forth this solution. And this seems to work.

感谢#Lopside 的解决方案，我修改了他的代码以提出这个解决方案。这似乎有效。

{       
Sub CheckValue()

Dim S1 As Worksheet
Dim S2 As Worksheet

Dim i As Integer
Dim k As Integer
Dim j As Integer

Set S1 = Worksheets("Sheet1")
Set S2 = Worksheets("Sheet2")

k = S1.UsedRange.Rows.Count
j = S2.UsedRange.Rows.Count

For i = 1 To k
If Application.WorksheetFunction.CountIf(S2.Range(S2.Cells(2, 1), S2.Cells(j, 1)), S1.Cells(i, 1).Value) > 0 Then
    S1.Cells(i, 5).Value = "Worked" 'If the value was found, enter "Worked" into column 5.
Else
    S1.Cells(i, 5).Value = "Not worked" 'If the value wasn't found, enter "Not worked" into column 5.
End If
Next i
End Sub

}

}