vba - 使用 VBA 通过所选单元格值的组合过滤表格 - 语法错误

Question

您能帮我解决下面的代码吗，我不断收到语法错误

宏的目标是通过同一列下的多行选择进行过滤。

我遇到语法错误的行是:

sColumn(n) = sColumn(n) & _
If(sColumn(n) vbNullString, ",", "") & oCell.Text


If sColumn(n) vbNullString Then

完整代码如下:

Sub combinationFilter()

Dim oRange As Range
Dim oArea As Range
Dim oCell As Range
Dim oLO As ListObject
Dim sColumn() As Variant
Dim n As Long

' Create Filter
Set oLO = Selection.ListObject

If Not oLO Is Nothing Then

    ReDim sColumn(1 To oLO.ListColumns.Count)

    Set oRange = Intersect(Selection, oLO.DataBodyRange)

    For Each oArea In oRange.Areas
        For Each oCell In oArea.Cells
            n = oCell.Column - oLO.Range.Column + 1
            sColumn(n) = sColumn(n) & _
            IIf(sColumn(n) vbNullString, ",", "") & oCell.Text
        Next oCell
    Next oArea

    ' Apply Filter
    For n = LBound(sColumn) To UBound(sColumn)
        If sColumn(n) like vbNullString Then
            oLO.Range.AutoFilter _
            Field:=n, _
            Criteria1:=Split(sColumn(n), ","), _
            Operator:=xlFilterValues
        End If
    Next n

End If

End Sub

我还在以下行中收到不匹配错误:

If sColumn(n) like vbNullString Then
oLO.Range.AutoFilter _
Field:=n, _
Criteria1:=Split(sColumn(n), ","), _
Operator:=xlFilterValues

提前谢谢您。

Answer 1

根据您的情况，正确的语法可能如下所示:

sColumn(n) = sColumn(n) & IIf(IsNull(sColumn(n)), "", ",") & oCell.Text

或者，正如成员 @Jeeped 所指出的，使用 Like 运算符而不是 Is (或使用等号 "="):

sColumn(n) = sColumn(n) & IIf(sColumn(n) Like vbNullString, "", ",") & oCell.Text

希望这会有所帮助。