基于另一工作表两列数据应用AutoFilter时出现类型不匹配错误

错误原因 #

Excel的AutoFilter方法无法直接通过数组传递多组配对的范围条件（即每个Scrip对应独立的Min%和Max%区间）。你使用的xlAnd操作符仅适用于单个条件的两个Criteria组合（比如单一的>=X 且 <=Y），无法对应数组中多组Min/Max的配对筛选，这直接导致了「Type mismatch」错误。

解决方案：使用高级筛选（AdvancedFilter） #

高级筛选支持自定义多组配对条件，能完美实现你的需求。以下是修改后的代码：

Private Sub ApplyFilter()
    Dim wsCombined As Worksheet
    Dim wsPercentageSetter As Worksheet
    Dim criteriaRange As Range
    Dim lastRowCombined As Long
    Dim lastRowSetter As Long
    Dim i As Long
    
    Set wsCombined = ThisWorkbook.Worksheets("Combined")
    Set wsPercentageSetter = ThisWorkbook.Worksheets("PercentageSetter")
    
    ' 清除之前的筛选
    wsCombined.AutoFilterMode = False
    
    ' 获取数据行号
    lastRowSetter = wsPercentageSetter.Cells(wsPercentageSetter.Rows.Count, "A").End(xlUp).Row
    lastRowCombined = wsCombined.Cells(wsCombined.Rows.Count, "A").End(xlUp).Row
    
    ' 构建高级筛选的条件区域：在PercentageSetter表后插入表头和条件行
    With wsPercentageSetter
        ' 写入条件表头（需与Combined表的列名对应）
        .Range("D1").Value = "Scrip"
        .Range("E1").Value = "Allocation %"
        .Range("F1").Value = "Allocation %"
        
        ' 写入每行的条件：Scrip匹配，Allocation% >= Min%，Allocation% <= Max%
        For i = 2 To lastRowSetter
            .Range("D" & i).Value = .Range("A" & i).Value
            .Range("E" & i).Value = ">=" & .Range("B" & i).Value
            .Range("F" & i).Value = "<=" & .Range("C" & i).Value
        Next i
        
        ' 定义条件区域范围
        Set criteriaRange = .Range("D1:F" & lastRowSetter)
    End With
    
    ' 应用高级筛选
    wsCombined.Range("A1:H" & lastRowCombined).AdvancedFilter _
        Action:=xlFilterInPlace, _
        CriteriaRange:=criteriaRange, _
        Unique:=False
        
    ' 可选：清理条件区域（如果不需要保留）
    ' wsPercentageSetter.Range("D1:F" & lastRowSetter).ClearContents
End Sub

代码说明 #

1. 条件区域构建：在PercentageSetter表的D-F列构建条件区域，表头与Combined表的列名一致，每行对应一个Scrip的匹配条件和Allocation%的范围限制。
2. 高级筛选执行：使用AdvancedFilter的xlFilterInPlace参数直接在原表筛选，无需复制数据。
3. 兼容性：该方法支持多组配对条件，完全避免了原AutoFilter的限制。

替代方案：辅助列法 #

如果不想用高级筛选，也可以在Combined表添加辅助列，计算每行是否符合条件，再筛选辅助列的True值：

Private Sub ApplyFilterWithHelperColumn()
    Dim wsCombined As Worksheet
    Dim wsPercentageSetter As Worksheet
    Dim lastRowCombined As Long
    Dim lastRowSetter As Long
    Dim helperCol As Long
    
    Set wsCombined = ThisWorkbook.Worksheets("Combined")
    Set wsPercentageSetter = ThisWorkbook.Worksheets("PercentageSetter")
    
    ' 清除之前的筛选
    wsCombined.AutoFilterMode = False
    
    ' 获取数据行号
    lastRowSetter = wsPercentageSetter.Cells(wsPercentageSetter.Rows.Count, "A").End(xlUp).Row
    lastRowCombined = wsCombined.Cells(wsCombined.Rows.Count, "A").End(xlUp).Row
    
    ' 定义辅助列（比如I列）
    helperCol = 9
    wsCombined.Cells(1, helperCol).Value = "符合条件"
    
    ' 用VLOOKUP匹配对应Scrip的Min和Max，判断是否在范围内
    With wsCombined.Range(wsCombined.Cells(2, helperCol), wsCombined.Cells(lastRowCombined, helperCol))
        .Formula = "=AND(ISNUMBER(MATCH(A2,'PercentageSetter'!$A$2:$A$" & lastRowSetter & ",0)),H2>=VLOOKUP(A2,'PercentageSetter'!$A$2:$C$" & lastRowSetter & ",2,FALSE),H2<=VLOOKUP(A2,'PercentageSetter'!$A$2:$C$" & lastRowSetter & ",3,FALSE))"
        .Value = .Value ' 转换为值，避免公式卡顿
    End With
    
    ' 筛选辅助列为True的行
    wsCombined.Range("A1:I" & lastRowCombined).AutoFilter Field:=helperCol, Criteria1:=True
    
    ' 可选：隐藏辅助列
    ' wsCombined.Columns(helperCol).Hidden = True
End Sub

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