时间戳

首页 > 解决方案 > 如何使用 Excel VBA 设置一个变量以过滤不同的文本字符串值？

excel - 如何使用 Excel VBA 设置一个变量以过滤不同的文本字符串值？

问题描述

我正在将文本文件复制到保存在变量 CleanerLog_sh 下的 Excel 工作表“CleanerLog”中。然后，我使用另一个名为“Filter_Criteria”的工作表将我想要过滤的项目输入到使用 For 循环的数组 FilterC() 中。然后，我使用该数组将过滤后的数据复制到另一个名为“OutputC”的工作表，该工作表保存在变量 OutputC_sh 下。

将数据复制到新工作表后，我想按最后一列进行过滤。我正在使用它来确定第 2 列中的项目得到了完整的配方。

这是我目前使用的代码：


Dim Filter_Criteria_sh As Worksheet
Dim CleanerLog_sh As Worksheet
Dim OutputC_sh As Worksheet

Set Filter_Criteria_sh = ThisWorkbook.Sheets("Filter_Criteria")
Set CleanerLog_sh = ThisWorkbook.Sheets("CleanerLog")
Set OutputC_sh = ThisWorkbook.Sheets("OutputC")

OutputC_sh.UsedRange.Clear

Dim Filter_C() As String
Dim n As Integer

n = Application.WorksheetFunction.CountA(Filter_Criteria_sh.Range("A:A")) - 2

ReDim Filter_C(n) As String

Dim i As Integer
For i = 0 To n
    Filter_C(i) = Filter_Criteria_sh.Range("A" & i + 2)
Next i

CleanerLog_sh.AutoFilterMode = False

CleanerLog_sh.UsedRange.AutoFilter 1, Filter_C(), xlFilterValues
CleanerLog_sh.UsedRange.Copy OutputC_sh.Range("A3")

CleanerLog_sh.AutoFilterMode = False

OutputC_sh.Range("$A$4:$AA$30000").AutoFilter Field:=4, Criteria1:= _
        "=Step 4 of 4 completed*", Operator:=xlAnd

在过滤发生之前，表当前看起来像这样：

60_Text_36 | D2W22 | Brush 2 | Step 2 of 4 completed in Brush 2 Recipe

60_Text_38 | D2W22 | Brush 2 | Step 3 of 4 completed in Brush 2 Recipe

60_Text_32 | D2W21 | Brush 1 | Step 1 of 4 completed in Brush 1 Recipe

60_Text_35 | D2W21 | Brush 1 | Step 2 of 4 completed in Brush 1 Recipe

60_Text_38 | D2W21 | Brush 1 | Step 3 of 4 completed in Brush 1 Recipe

60_Text_39 | D2W22 | Brush 2 | Step 4 of 4 completed in Brush 2 Recipe

60_Text_40 | D2W21 | Brush 1 | Step 4 of 4 completed in Brush 1 Recipe

60_Text_42 | D2W21 | Brush 2 | Step 1 of 4 completed in Brush 2 Recipe

我需要过滤掉最后一列的结束步骤（第 4 步，共 4 步），以便对于第 2 列中的每个项目，我们找到每个画笔的结束步骤：

00:35.4 | 60_Text_39 | D2W22 | Brush 2 | Step 4 of 4 completed in Brush 2 Recipe

00:45.4 | 60_Text_40 | D2W21 | Brush 1 | Step 4 of 4 completed in Brush 1 Recipe

我的问题是，有时复制到“CleanerLog”工作表中的数据有一个不同的结束步骤，例如“Step 5 of 5”而不是“Step 4 of 4”。

如何使以下代码中的过滤条件成为搜索配方中最后一步的变量？

OutputC_sh.Range("$A$4:$AA$30000").AutoFilter Field:=4, Criteria1:= _
        "=Step 4 of 4 completed*", Operator:=xlAnd

标签： excelvbafilter

解决方案

扫描输出表上的行以构建一个唯一值字典，其中数字相等。使用字典键作为输出表自动过滤器中的条件。例如 ;

 Option Explicit


 Sub createOutput()
   Dim wsFilter, wsLog, wsOutput As Worksheet
   With ThisWorkbook
     Set wsFilter = .Sheets("Filter_Criteria")
     Set wsLog = .Sheets("CleanerLog")
     Set wsOutput = .Sheets("OutputC")
   End With
   wsOutput.UsedRange.Clear

   ' get filter row 1 values
   Dim endCell As Range
   With wsFilter
     Set endCell = .Cells(1, .Columns.Count).End(xlToLeft)
   End With

   ' build filter array
   Dim sText As String, sFilter_C() As String, cell As Range
   ReDim sFilter_C(endCell.Column)
   Dim i As Long

   With wsFilter
   i = 0
   For Each cell In .Range(.Range("C1"), endCell)
     'Debug.Print cell.Address, cell.Value
     If Len(cell.Value) > 0 Then
       sFilter_C(i) = cell.Value
       i = i + 1
       sText = sText & cell.Value & vbCrLf
     End If
   Next cell
   End With
   MsgBox "Filter Coll A is  " & vbCrLf & sText

   ' apply filter as copy to output
   With wsLog
     .AutoFilterMode = False
     .UsedRange.AutoFilter 1, sFilter_C, xlFilterValues
     .UsedRange.Copy wsOutput.Range("A3")
     .AutoFilterMode = False
   End With
   wsOutput.Select

   ' build output filter list on commplete steps
   Dim ar, dict, txt As String
   Set dict = CreateObject("Scripting.Dictionary")
   For Each cell In wsOutput.UsedRange.Columns(4).Cells
       ' split sentence by space
       ' Step 3 of 4 completed in Brush 2 Recipe
       txt = cell.Value
       If Left(txt, 4) = "Step" Then
         ar = Split(txt, " ", 5)
         Debug.Print ar(0), ar(1), ar(2), ar(3)
         If ar(1) = ar(3) Then
           If Not dict.Exists(txt) Then
             dict.Add txt, 1
           End If
         End If
       End If
   Next

   ' apply filter
   wsOutput.UsedRange.AutoFilter 4, dict.keys, xlFilterValues

 End Sub

推荐阅读