[算表] Excel VBA多重模糊篩選

[home0303 (Blue night)]

軟體:EXCEL

版本:2010

想設計一種可以一次進行多條件的模糊篩選功能

編了以下的碼

Sub 多重模糊篩選()
    MM = InputBox("條件有幾個?")
    ActiveCell.EntireColumn.Select
    Select Case MM
    Case 1
    MM1 = InputBox("條件一")
    Selection.AutoFilter Field:=1, Criteria1:="*" & MM1 & "*"
    Case 2
    MM1 = InputBox("條件一")
    MM2 = InputBox("條件二")
    Selection.AutoFilter Field:=1, Criteria1:="*" & MM1 & "*",
Operator:=xlOr, Criteria2:="*" & MM2 & "*"
    Case 3
    MM1 = InputBox("條件一")
    MM2 = InputBox("條件二")
    MM3 = InputBox("條件三")
    Selection.AutoFilter Field:=1, Criteria1:="=*" & MM1 & "*",
Operator:=xlOr, Criteria2:="=*" & MM2 & "*", Operator:=xlOr, Criteria3:="=*"
& MM3 & "*"
    Case 4
    MM1 = InputBox("條件一")
    MM2 = InputBox("條件二")
    MM3 = InputBox("條件三")
    MM4 = InputBox("條件四")
    Selection.AutoFilter Field:=1, Criteria1:="*" & MM1 & "*",
Operator:=xlOr, Criteria2:="*" & MM2 & "*", Operator:=xlOr, Criteria3:="*" &
MM3 & "*", Operator:=xlOr, Criteria4:="*" & MM4 & "*"
    Case 5
    MM1 = InputBox("條件一")
    MM2 = InputBox("條件二")
    MM3 = InputBox("條件三")
    MM4 = InputBox("條件四")
    MM5 = InputBox("條件五")
    Selection.AutoFilter Field:=1, Criteria1:="*" & MM1 & "*",
Operator:=xlOr, Criteria2:="*" & MM2 & "*", Operator:=xlOr, Criteria3:="*" &
MM3 & "*", Operator:=xlOr, Criteria4:="*" & MM4 & "*", Operator:=xlOr,
Criteria5:="*" & MM5 & "*"
    Case 6
    MM1 = InputBox("條件一")
    MM2 = InputBox("條件二")
    MM3 = InputBox("條件三")
    MM4 = InputBox("條件四")
    MM5 = InputBox("條件五")
    MM6 = InputBox("條件六")
    Selection.AutoFilter Field:=1, Criteria1:="*" & MM1 & "*",
Operator:=xlOr, Criteria2:="*" & MM2 & "*", Operator:=xlOr, Criteria3:="*" &
MM3 & "*", Operator:=xlOr, Criteria4:="*" & MM4 & "*", Operator:=xlOr,
Criteria5:="*" & MM5 & "*", Operator:=xlOr, Criteria6:="*" & MM6 & "*"
    End Select
End Sub


目前暫定條件在6個以內   由使用者自己決定

但這如果條件3個以上就錯誤失敗了

請問能做怎樣的修正讓他運作嗎

謝謝


--



--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 223.136.230.175
※ 文章網址: https://www.ptt.cc/bbs/Office/M.1479195935.A.014.html
→ soyoso: 如可用輔助欄的話，改以range.advancedfilter試試
→ home0303: 懂了   謝謝S大