VBA合并查找函数异常：$K$2非空时无法过滤数据求助

我用以下代码实现数据查找合并功能，目前遇到异常：当单元格$K$2不为空时，本该按该值过滤数据，但实际无论何种条件都显示全部数据，暂未定位故障点。

需求条件 #

• 若$K$2为空，显示全部匹配日期的数据
• 若$K$2不为空，仅显示匹配日期且符合$K$2值过滤的数据

当前使用的公式代码 #

=@IF(G7="", "", Merged_Lookup_Concat(G7, Table1[Date and time], $K$2, Table1[Title], Table1[Time and Title]))

自定义VBA函数代码 #

Function Merged_Lookup_Concat(SearchDate As String, _
                               SDate As Range, SearchValue As String, _
                               SValue As Range, ReturnCol As Range, Optional K2Value As String) As String
    Dim i As Long
    Dim result As String
    Dim j As Integer
    j = 0

    If K2Value <> "" Then
 For i = 1 To SDate.Cells.CountLarge
    Debug.Print "SearchValue: " & SearchValue
    Debug.Print "SValue: " & SValue.Cells(i).value
    If Int(SDate.Cells(i).value) = SearchDate Then
        If SValue.Cells(i).value = SearchValue Or SValue.Cells(i).value = "" Then
            If j >= 5 Then
                result = result & "...more..."
                Exit For
            End If
            result = result & Left(ReturnCol.Cells(i).value, 25) & vbNewLine
            j = j + 1
        End If
    End If
Next i
    Else
        For i = 1 To SDate.Cells.CountLarge
            If Int(SDate.Cells(i)) = SearchDate Then
                If j >= 5 Then
                    result = result & "...more..."
                    Exit For
                End If
                result = result & Left(ReturnCol.Cells(i), 25) & vbNewLine
                j = j + 1
            End If
        Next i
    End If

    Merged_Lookup_Concat = Trim(result)
End Function

故障根源与修复方案 #

核心问题 #

调用函数时参数传递完全错误：你把$K$2的值传给了SearchValue参数，但函数里判断过滤逻辑的是可选参数K2Value——这个参数从未被赋值，永远是空字符串，导致代码一直走Else分支（显示全部数据）。

另外还有两处次要问题：

1. SearchDate定义为String，但Int(SDate.Cells(i).value)是数值型，直接比较存在类型不匹配风险
2. Left(ReturnCol.Cells(i), 25)缺失.value属性，可能导致异常

修复步骤 #

1. 修正公式调用（关键）

把$K$2的值传给正确的K2Value参数，调整后的公式：

=@IF(G7="", "", Merged_Lookup_Concat(G7, Table1[Date and time], Table1[Title], Table1[Time and Title], , $K$2))

或者用指名参数的方式更清晰：

=@IF(G7="", "", Merged_Lookup_Concat(SearchDate:=G7, SDate:=Table1[Date and time], SValue:=Table1[Title], ReturnCol:=Table1[Time and Title], K2Value:=$K$2))

2. 修正VBA函数代码

Function Merged_Lookup_Concat(SearchDate As Variant, _
                               SDate As Range, SValue As Range, _
                               ReturnCol As Range, Optional K2Value As String = "") As String
    Dim i As Long
    Dim result As String
    Dim j As Integer
    j = 0
    Dim targetDate As Date
    
    ' 处理日期类型转换，避免字符串与数值比较的错误
    If IsDate(SearchDate) Then
        targetDate = Int(SearchDate)
    Else
        Merged_Lookup_Concat = ""
        Exit Function
    End If

    If K2Value <> "" Then
        For i = 1 To SDate.Cells.CountLarge
            If Int(SDate.Cells(i).Value) = targetDate Then
                ' 按K2的值过滤，若需要保留空Title的记录，可添加 Or SValue.Cells(i).Value = ""
                If SValue.Cells(i).Value = K2Value Then
                    If j >= 5 Then
                        result = result & "...more..."
                        Exit For
                    End If
                    result = result & Left(ReturnCol.Cells(i).Value, 25) & vbNewLine
                    j = j + 1
                End If
            End If
        Next i
    Else
        For i = 1 To SDate.Cells.CountLarge
            If Int(SDate.Cells(i).Value) = targetDate Then
                If j >= 5 Then
                    result = result & "...more..."
                    Exit For
                End If
                result = result & Left(ReturnCol.Cells(i).Value, 25) & vbNewLine
                j = j + 1
            End If
        Next i
    End If

    Merged_Lookup_Concat = Trim(result)
End Function

修复说明 #

• 调整参数顺序，将可选参数K2Value放在最后，避免调用混淆
• 优化日期类型处理，避免字符串与数值比较的错误
• 修正过滤逻辑，用K2Value匹配SValue，而非原代码中错误使用的SearchValue
• 补全Left函数中缺失的.Value属性
• 若需要保留Title为空的记录，可将过滤条件改回If SValue.Cells(i).Value = K2Value Or SValue.Cells(i).Value = "" Then

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