如何通过循环匹配两个Excel工作表中的任务并更新状态？

需求说明 #

• 两个工作表：Daily1和Task List，各有一列任务
• 遍历Daily1中从B7开始的所有任务（直到空单元格）
• 在Task List中找到对应匹配的任务
• 检查Daily1中当前任务右侧偏移7列的单元格值：
  • 若为CR，则在Task List匹配任务右侧偏移2列填入Completed
  • 否则填入Incomplete

原错误代码 #

Private Sub Validation()

Dim wsDaily As Worksheet
Dim wsTskL As Worksheet
Dim rngD As Range
Dim rngTL As Range

Set wsDaily = Worksheets("Daily1")
Set wsTskL = Worksheets("Task List")

Set rngD = wsDaily.Range("B7")
Set rngTL = wsTskL.Range("C2")

Do Until rngD.Value = ""
    If COUNTIF(Taks List!wsTskL,Daily!rngD.value)>0 == True Then
       If rngD.Offset(0, 7).Value = "CR" Then
            rngTL.Offset(0, 2).Value = "Completed"
            Else
            rngTL.Offset(0, 2).Value = "Incomplete"
        End If
    End If
    Set rngD = rngD.Offset(1)
Loop
End Sub

原代码问题点 #

• COUNTIF用法错误：VBA中需调用WorksheetFunction.CountIf，且参数应为「查找范围」和「查找值」，原代码混淆了工作表对象和范围，还存在拼写错误（Taks List应为Task List）
• 匹配逻辑错误：固定rngTL在C2，未根据Daily1的任务找到Task List中对应的行，导致所有结果都写入同一位置
• 语法冗余：>0 == True多余，VBA中直接用CountIf(...) > 0即可作为判断条件
• 引用格式错误：Daily!rngD.value这种写法不符合VBA语法，应直接用变量rngD.Value

修正后的代码 #

Private Sub Validation()
    Dim wsDaily As Worksheet
    Dim wsTskL As Worksheet
    Dim rngD As Range
    Dim matchCell As Range ' 存储Task List中的匹配单元格
    
    ' 初始化工作表对象
    Set wsDaily = ThisWorkbook.Worksheets("Daily1")
    Set wsTskL = ThisWorkbook.Worksheets("Task List")
    
    ' 从Daily1的B7开始遍历任务
    Set rngD = wsDaily.Range("B7")
    Do Until rngD.Value = ""
        ' 在Task List的C列精准查找当前任务
        Set matchCell = wsTskL.Range("C:C").Find(What:=rngD.Value, LookIn:=xlValues, LookAt:=xlWhole)
        
        ' 如果找到匹配项
        If Not matchCell Is Nothing Then
            ' 根据Daily1中偏移7列的值设置状态，用UCase避免大小写问题
            If UCase(rngD.Offset(0, 7).Value) = "CR" Then
                matchCell.Offset(0, 2).Value = "Completed"
            Else
                matchCell.Offset(0, 2).Value = "Incomplete"
            End If
        End If
        
        ' 移动到下一个任务
        Set rngD = rngD.Offset(1)
    Loop
End Sub

代码说明 #

• 使用Range.Find精准定位Task List中的匹配任务，确保写入对应行的目标列
• 加入UCase统一转换为大写，避免因大小写不一致导致的匹配失败
• 明确工作表引用，避免因活动工作表变化导致的错误
• 增加matchCell Is Nothing判断，处理找不到匹配项的情况

内容的提问来源于stack exchange，提问作者David Rojas