Excel自动过滤器范围设置组合框？ [英] Excel AutoFilter range set by combo boxes?

问题描述

我目前正在开发一个工作表的熊，该工作表旨在使用 http://peltiertech.com/excel/chartshowto/panelunevenscales.html 。

I am currently working on a bear of a worksheet that is designed to show the change of individual parameters over the same time interval using the Backdoor technique detailed at http://peltiertech.com/excel/chartshowto/panelunevenscales.html.

基本上，我将它设置为一个模板，其中5列数据（行的长度不同）可以从第12行开始粘贴到列AF。我想要过滤的时间列在A列，我基本上想要使2个组合框（ComboBox1和ComboBox2）可以用于设置上限和下限时间，并过滤掉任何其他数据。我希望我可以把它变成一个动态图表，将调整大小的X轴只显示所选的时间段。

Basically, I have it set up as a template where 5 columns of data (which vary in length of rows) can be pasted into columns A-F starting at row 12. The time column that I would like to filter is in column A and I basically want to make it so that 2 combo boxes (ComboBox1 and ComboBox2) can be used to set an upper and lower time limit and filter out any other data. I'm hoping I can turn this into a dynamic chart that will resize the x-axis to show only the selected time period.

我还是比较新的使用VBA，我做了一个很好的搜索，我唯一可以发现，似乎相关的问题回答在 http://www.ozgrid.com/forum/showthread.php?t=54376 。我尝试下面的代码，并不断收到错误：

I'm still relatively new to using VBA and I did a good bit of searching and the only thing I could find that seemed related was the question answered at http://www.ozgrid.com/forum/showthread.php?t=54376. I tried the following code and kept getting errors:

Sub TimeFilter()

Crit1 = Format(ComboBox1, "0")

Crit2 = Format(ComboBox2, "0")

.AutoFilterMode = False

.Range("A12:L10000").AutoFilter

.Range("A12:L10000").AutoFilter Field:=1, Criteria:=">=" & crit1, Operator:=xlAnd, Criteria2:="<=" & crit2

End Sub

任何想法为什么它可能不工作？欢迎任何帮助或指导！

Any ideas on why it might not be working? Any help or guidance is appreciated!

****编辑****

****EDIT****

这里是更新我使用的电子表格的完整代码，并与TimeFilter脚本我得到一个运行时错误'1004'，范围类的AutoFilter方法失败：
**我过滤的时间是在0.2秒间隔，格式为MM：SS.X。

Here is the updated FULL code for the spreadsheet I'm using, and with the TimeFilter script I get a "Run-time error '1004', AutoFilter method of Range class failed": **The time that I am filtering by is in 0.2 second intervals with the format MM:SS.X.

Sub CreateComboBoxes()

Dim Cell As Range
Dim sht As Worksheet

Set sht = ThisWorkbook.Worksheets("Sheet1")

    Set Cell = Range("I8")  
    With Cell
        sht.DropDowns.Add(.Left, .Top, .Width, .Height).Name = "ComboBox1"
    End With

    Set Cell = Range("K8")    
    With Cell
        sht.DropDowns.Add(.Left, .Top, .Width, .Height).Name = "ComboBox2"
    End With

End Sub

Sub TimeFilter()

Crit1 = Format(ComboBox1, "0")
Crit2 = Format(ComboBox2, "0")

Worksheets("Sheet1").AutoFilterMode = False
Worksheets("Sheet1").Range("A12:L10000").AutoFilter
Worksheets("Sheet1").Range("A12:L10000").AutoFilter Field:=1,Criteria1:=">=" & Crit1, Operator:=xlAnd, _
Criteria2:="<=" & Crit2

End Sub

推荐答案

我先说这不是一个答案，而是使用 with ，结束于语句的解释。

I preface this by stating that this is not an answer but an explanation in the use of With, End With statement. The comment section does not offer the formatting tools required to do so properly

使用，

    ThisWorkbook.Worksheets("Sheet1").UsedRange.Font.ColorIndex = 3
    ThisWorkbook.Worksheets("Sheet1").UsedRange.Font.Bold = True
    ThisWorkbook.Worksheets("Sheet1").UsedRange.Font.Size = 15
    ThisWorkbook.Worksheets("Sheet1").UsedRange.Font.Name = "Times new Roman"

也可以写成

    With ThisWorkbook.Worksheets("Sheet1").UsedRange.Font
        .ColorIndex = 3
        .Bold = True
        .Size = 15
        .Name = "Times new Roman"
    End With

您会注意到。在与end语句之间的行之前，这意味着它应该被看作是由 With 语句设置的继续。

You'll notice the "." in front of the lines in between the with end with statement, this means it should be read as a continuation of what is set by the With statement.

这篇关于Excel自动过滤器范围设置组合框？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！