msmplay

回復 10# 准提部林


   謝謝准大~~~~~~~~~~~~~~~~

GBKEE

回復 11# msmplay


  

下載 (30.19 KB)

2016-12-18 08:09

1. Option Explicit
   
2. Sub Ex_作業天數()
   
3.     Ex_篩選 "作業天數", 10, 3   'xlTop10Items=3 請看圖示  
   
4. End Sub
   
5. Sub Ex_完成率()
   
6.     Ex_篩選 "完成率", "<0.9", 0
   
7. End Sub
   
8. Sub Ex_逾期數()
   
9.     Ex_篩選 "逾期數", 10, 3
   
10. End Sub
    
11. Sub Ex_篩選(篩選 As String, 篩選準則 As String, xl_Operator As Integer)
    
12.     Dim Rng As Range
    
13.     With Sheets("工作完成率統計")
    
14.         Set Rng = .Rows("1:3").Find(篩選, lookat:=xlWhole)
    
15.         If Rng Is Nothing Then MsgBox "找不到  " & 篩選: Exit Sub
    
16.         Set Rng = Range(Rng.Range("A3"), Rng.Range("A4").End(xlDown).Offset(-1))
    
17.         If .FilterMode Or .AutoFilterMode Then .AutoFilterMode = False   ' 取消 [自動篩選]
    
18.         If xl_Operator > 0 Then
    
19.             Rng.AutoFilter Field:=1, Criteria1:=篩選準則, Operator:=xl_Operator ', VisibleDropDown:=False
    
20.         Else
    
21.             Rng.AutoFilter Field:=1, Criteria1:=篩選準則 ', VisibleDropDown:=False
    
22.         End If
    
23.         '**  VisibleDropDown:=False :不在工作表上顯示有 [自動篩選] 下拉箭號
    
24.         Rng(1).Select
    
25.     End With
    
26. End Sub

複製代碼

msmplay

回復 12# GBKEE


   謝謝G大熱心幫忙ㄟ~~~~~~~~~~