时间戳

首页 > 解决方案 > VBA - 自动过滤器数组中的连接字符串不起作用

vba - VBA - 自动过滤器数组中的连接字符串不起作用

问题描述

因此，我将所有变量存储在一个字符串中，然后尝试将其传递给 Autofilter 标准，但无济于事。

Sub TestAutoFilter()
'Filter based on column Importance.
'PURPOSE: Dynamically Create Array Variable based on a Given Size

Dim myArray()   As Variant
Dim DataRange   As Range
Dim cell        As Range
Dim x           As Long  

'Determine the data you want stored
Set DataRange = ActiveSheet.UsedRange

'Resize Array prior to loading data

'Loop through each cell in Range and store value in Array
counter = 0
For Each chkbx In ThisWorkbook.Sheets("Sheet1").CheckBoxes
    
    If chkbx.Value > 0 And InStr(1, LCase(chkbx.Name), "check") <> 0 Then
        counter = counter + 1
    End If
Next chkbx

ReDim myArray(counter)
For Each chkbox In ThisWorkbook.Sheets("Sheet1").CheckBoxes
    If chkbox.Value > 0 And InStr(1, LCase(chkbox.Name), "check") <> 0 Then
        myArray(x) = chkbox.Caption
        x = x + 1
    End If
Next chkbox

myArrayString = ""
'Print values to Immediate Window (Ctrl + G to view)
For x = LBound(myArray) To counter
    myArrayString = myArrayString & "," & myArray(x)
Next x
'Debug.Print (myArrayString)

myRightString = Left(myArrayString, Len(myArrayString) - 1)
myLeftString = Right(myRightString, Len(myRightString) - 1)
'Debug.Print (RTrim(myLeftString))
ThisWorkbook.Sheets("Data").Range("A3:F3").AutoFilter Field:=3, _
                         Criteria1:=Array(myLeftString), _
                         Operator:=xlFilterValues

End Sub

我还尝试将 myLeftstring 替换为实际的数组，Criteria1:=myArray但这也没有用。

标签： vbaexcel-2016

解决方案

推荐阅读