VBA-遍历多个工作表并应用过滤器 [英] VBA - Loop Through Multiple Worksheets and Apply Filter

问题描述

我需要对同一工作簿中的所有工作表应用相同的过滤器.

所有工作表的第一行都有标题，但是应该应用该过滤器的标题名称并不总是在同一列中，即工作表2中标题为H的工作表它在A列中，在工作表3中在L列中，依此类推...

另外，某些标头将与条件不完全匹配-即，某些标头将以"STATUS"作为标头，某些标头将为" prefix _Status"，其他标头将为"CurrentStatus"等等，所以我需要使用Instr函数(除非有更好的选择)，但我似乎无法弄清楚在哪里或如何使用它.

这是我到目前为止的代码:

Sub WorksheetLoop()

         Dim WS_Count As Integer
         Dim I As Integer

         ' Set WS_Count equal to the number of worksheets in the active
         ' workbook.
         WS_Count = ActiveWorkbook.Worksheets.count

         ' Begin the loop.
         For I = 1 To WS_Count

            Dim count As Integer, rngData As Range
            Set rngData = Range("A1").CurrentRegion

            count = Application.WorksheetFunction.Match("STATUS", Range("A1:AZ1"), 0)

            rngData.autofilter Field:=count, Criteria1:="INACTIVE"

         Next I

End Sub

此代码仅将过滤器应用于一张纸.

解决方案

只要您未在代码中显式指定工作表，便始终引用ActiveSheet.因此，在Range()中，您必须像这样参考工作表:

发件人:

Set rngData = Range("A1").CurrentRegion
count = Application.WorksheetFunction.Match("STATUS", Range("A1:AZ1"), 0)

更改为:

With Worksheets(I)
    Set rngData = .Range("A1").CurrentRegion
    count = Application.WorksheetFunction.Match("STATUS", .Range("A1:AZ1"), 0)
End With

With Worksheets(I) - End With之间的代码中的点是有区别的:

---

关于Application.WorksheetFunction.Match，它仅匹配包含恰好单词"STATUS"的单元格.如果前面还有空格或后面有符号，那是个好主意:

count = Application.Match("*STATUS*", Worksheets(1).Range("A1:AZ1"), 0)

然后仍然需要检查.像这样:

If Not IsError(count) Then
    rngData.autofilter Field:=count, Criteria1:="INACTIVE"
End If

---

关于问题的第二部分，在Match函数中的值周围使用*:

Public Sub TestMe()    
    Range("E1") = "5teSt34"
    Debug.Print Application.WorksheetFunction.Match("*Test*", Range("A1:H1"), 0)    
End Sub

将始终返回5.

I need to apply the same filter to all the worksheets in the same workbook.

All the sheets have headers in the first row, but the header name that the filter is supposed to be applied to is not always in the same column, i.e, worksheet one the header in question is in column H, in worksheet 2 it's in column A, in worksheet 3 it's in column L and so on...

Also, some of the headers will not be an exact match for the criteria - i.e, some will have "STATUS" as the header, some will be "prefix_Status", others "CurrentStatus" and so on.. So I need to use the Instr funciton (Unless there's some better option) but I cannot seem to figure out where or how to use it..

Here is the code I have so far:

Sub WorksheetLoop()

         Dim WS_Count As Integer
         Dim I As Integer

         ' Set WS_Count equal to the number of worksheets in the active
         ' workbook.
         WS_Count = ActiveWorkbook.Worksheets.count

         ' Begin the loop.
         For I = 1 To WS_Count

            Dim count As Integer, rngData As Range
            Set rngData = Range("A1").CurrentRegion

            count = Application.WorksheetFunction.Match("STATUS", Range("A1:AZ1"), 0)

            rngData.autofilter Field:=count, Criteria1:="INACTIVE"

         Next I

End Sub

This code applies the filter to only ONE sheet.

解决方案

You are referring always to the ActiveSheet, whenever you do not specify the worksheet explicitly in your code. Thus, in the Range() you have to refer to the worksheet like this:

From:

Set rngData = Range("A1").CurrentRegion
count = Application.WorksheetFunction.Match("STATUS", Range("A1:AZ1"), 0)

Change to:

With Worksheets(I)
    Set rngData = .Range("A1").CurrentRegion
    count = Application.WorksheetFunction.Match("STATUS", .Range("A1:AZ1"), 0)
End With

The dots in the code between With Worksheets(I) - End With are what makes the difference:

---

Concerning the Application.WorksheetFunction.Match, it only matches cells which contain exactly the word "STATUS". If there is something else like a space before or a sign after, then something like this is a good idea:

count = Application.Match("*STATUS*", Worksheets(1).Range("A1:AZ1"), 0)

Then a check is still needed. Like this:

If Not IsError(count) Then
    rngData.autofilter Field:=count, Criteria1:="INACTIVE"
End If

---

Concerning the second part of the question, use * around the value in the Match function:

Public Sub TestMe()    
    Range("E1") = "5teSt34"
    Debug.Print Application.WorksheetFunction.Match("*Test*", Range("A1:H1"), 0)    
End Sub

Will always return 5.

这篇关于VBA-遍历多个工作表并应用过滤器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！