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

看板Office作者home0303 (Blue night)時間9年前 (2016/11/15 15:45)推噓0(0推 0噓 2→)

留言2則, 2人參與討論串1/1

軟體: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