excel - 如果单元格范围等于另一个工作表中的单元格范围，则添加注释

Question

我在一个工作表中有一列单元格，我想针对另一个工作表中的列进行验证。
如果有匹配，那么我想添加评论。
但是，当我尝试运行代码时，它没有按预期添加注释。
评论高度赞赏！

Sub Checktabfour()

Dim i As Long
Dim j As Long
Dim k As Long
j = Sheets(5).Range("C" & Rows.Count).End(xlUp).Row
k = Sheets(4).Range("B" & Rows.Count).End(xlUp).Row

For i = 9 To k
If Cells(i, "B").Value <> "" And Cells(i, "B").Value = Sheets(5).Range("C" & j).Value Then
Cells(i, "D").Value = "Yes"

End If
Next i
End Sub

标签： excelvba

Answer 1

匹配时添加注释 ( For Each ... Next, Application.Match)

• 这是一个“范围研究”，它肯定可以使用数组来改进。
• 适当调整C2（Source Worksheet第一行未知）。

Option Explicit

Sub CheckFourAgainstFive()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim sws As Worksheet: Set sws = wb.Sheets(5)
    Dim slCell As Range: Set slCell = sws.Range("C" & sws.Rows.Count).End(xlUp)
    Dim srg As Range: Set srg = sws.Range("C2", slCell)
    
    Dim dws As Worksheet: Set dws = wb.Sheets(4)
    Dim dlCell As Range: Set dlCell = dws.Range("B" & dws.Rows.Count).End(xlUp)
    Dim drg As Range: Set drg = dws.Range("B9", dlCell)
 
    Dim dCell As Range
    Dim cValue As Variant

    For Each dCell In drg.Cells
        cValue = dCell.Value
        If Not IsError(cValue) Then
            If Len(cValue) > 0 Then
                If IsNumeric(Application.Match(cValue, srg, 0)) Then
                    dCell.EntireRow.Columns("D").Value = "Yes"
                End If
            End If
        End If
    Next dCell
    
End Sub