返回列表 上一主題 發帖

[發問] VBA依條件自動篩選

假如第1名有2個, 第2名有3個, 前5大, 應是8個???

TOP

  1. Dim FilArea As Range

  2. Sub 逾期數()
  3. Dim R&, xClmn As Range, i&, LG, GG, N%
  4. Call 取消篩選
  5. Set xClmn = FilArea.Columns(103)
  6. R = Application.CountIf(xClmn, ">0")
  7. If R = 0 Then Exit Sub
  8. Sheets("工作完成率統計").AutoFilterMode = False
  9. For i = 1 To R
  10.     LG = Application.Large(xClmn, i)
  11.     If GG <> LG Then N = N + 1: GG = LG
  12.     If N = 5 Then Exit For
  13. Next
  14. FilArea.AutoFilter Field:=103, Criteria1:=">=" & GG
  15. End Sub

  16. Sub 低於90()
  17. Call 取消篩選
  18. Sheets("工作完成率統計").AutoFilterMode = False
  19. FilArea.AutoFilter Field:=104, Criteria1:="<90%"
  20. End Sub

  21. Sub 作業天數()
  22. Dim R&, xClmn As Range, i&, LG, GG, N%
  23. Call 取消篩選
  24. Set xClmn = FilArea.Columns(105)
  25. R = Application.Count(xClmn)
  26. If R = 0 Then Exit Sub
  27. Sheets("工作完成率統計").AutoFilterMode = False
  28. For i = 1 To R
  29.     LG = Application.Large(xClmn, i)
  30.     If GG <> LG Then N = N + 1: GG = LG
  31.     If N = 5 Then Exit For
  32. Next
  33. FilArea.AutoFilter Field:=105, Criteria1:=">=" & GG
  34. End Sub

  35. Sub 取消篩選()
  36. Dim R&
  37. With Sheets("工作完成率統計")
  38.      .AutoFilterMode = False
  39.      R = .UsedRange.Rows.Count - 1
  40.      If R <= 4 Then Exit Sub
  41.      Set FilArea = .Range("B3:DB" & R)
  42.      FilArea.AutoFilter
  43. End With
  44. End Sub
複製代碼
[attach]26115[/attach]

管理總表_V1.rar (60.33 KB)

TOP

        靜思自在 : 口說好話、心想好意、身行好事。
返回列表 上一主題