当单元格C2的公式值变化时触发事件及数据查询需求

问题背景 #

• 需求：当单元格C2（公式为=B2）的值变化时，触发事件逻辑——用更新后的C2值在另一工作表的Table1中匹配Y列等于C2的记录，提取X列的唯一ID生成记录集
• 现状：使用Sheet1的Worksheet_Change事件时，手动修改C2能触发，但B2（下拉列表）变化导致C2公式计算值更新时，该事件无法触发，且不愿修改B2的属性

解决方案 #

Worksheet_Change仅响应手动编辑的单元格，公式计算导致的值变化需要用Worksheet_Calculate事件。为避免每次计算都触发逻辑，需记录C2的旧值，仅当新旧值不同时执行匹配逻辑。

代码实现 #

在Sheet1的代码模块中插入以下代码：

Private prevC2Value As Variant

Private Sub Worksheet_Activate()
    ' 激活工作表时初始化C2旧值
    prevC2Value = Me.Range("C2").Value
End Sub

Private Sub Worksheet_Calculate()
    Dim currentC2Value As Variant
    Dim targetTable As ListObject
    Dim matchColumn As Range
    Dim cell As Range
    Dim uniqueIDs As Collection
    Dim id As Variant
    
    currentC2Value = Me.Range("C2").Value
    
    ' 仅当C2值确实变化时执行后续逻辑
    If currentC2Value <> prevC2Value Then
        Set uniqueIDs = New Collection
        
        ' 引用Table1，替换为实际工作表名称
        Set targetTable = ThisWorkbook.Worksheets("Table1所在工作表名").ListObjects("Table1")
        ' 引用Table1的Y列数据区域，替换为实际列名
        Set matchColumn = targetTable.ListColumns("Y").DataBodyRange
        
        ' 循环匹配并收集唯一ID
        On Error Resume Next ' 忽略重复ID的添加错误
        For Each cell In matchColumn
            If cell.Value = currentC2Value Then
                ' 提取对应X列的值，替换为实际列名
                Dim currentID As Variant
                currentID = targetTable.ListColumns("X").DataBodyRange(cell.Row - targetTable.HeaderRowRange.Row).Value
                uniqueIDs.Add currentID, Key:=CStr(currentID)
            End If
        Next cell
        On Error GoTo 0
        
        ' 处理生成的唯一ID记录集，示例：输出到Sheet1的A列
        Me.Range("A:A").ClearContents
        For Each id In uniqueIDs
            Me.Cells(Me.Rows.Count, "A").End(xlUp).Offset(1, 0).Value = id
        Next id
        
        ' 更新旧值，避免重复触发
        prevC2Value = currentC2Value
    End If
End Sub

注意事项 #

• 替换代码中的Table1所在工作表名为实际存放Table1的工作表名称
• 替换X和Y为Table1中对应的列名
• Worksheet_Activate用于初始化旧值，防止工作表激活时误触发逻辑
• 使用Collection的Key属性自动实现ID去重，确保记录集中的ID唯一

内容的提问来源于stack exchange，提问作者Dasal Kalubowila