excel宏基于sheet1值进行过滤 [英] excel macro to filter based on sheet1 values

问题描述

Dim Yarr，Marr，Warr，Rarr As Variant

'sheet1包含以下信息，选择值和sheet2中的过滤器

Marr =数组（"1月"）'我希望这是动态范围

Warr =数组（" wk1"）

Rarr =数组（" x"，" y"）

'上面的过滤器应该应用于sheet2

范围（"B1"）。自动筛选字段：= 2，标准1：=马尔，运算符：= xlFilterValues

范围（"D1"）。自动筛选字段：= 4，标准1：= Warr，运算符：= xlFilterValues

范围（" A1"）。AutoFilter字段：= 1，Criteria1：= Rarr，运算符：= xlFilterValues

我需要excel宏来根据sheet1标准过滤sheet2，下面是要考虑的几个要点

- 宏应该使sheet1全部列到动态范围，因为范围会根据用户从列表框中的选择而变化 

，如示例2所示，月份范围分机1月和2月以两行结束，因此下次用户可以选择所有7个月，因此我们必须动态月，周和名称的范围  

- 宏应该过滤器基于sheet1从Column1到Column4一个接一个地开始

如示例1所示，宏应首先选择Year并过滤2017年和第二个选择月过滤器1月和第3个选择周和过滤器为wk1和第四个选择名称和过滤器为x，y（我们可以使用ubound，但我不擅长数组）

sheet1是从列表框中选择数据的值，它是各种各样的如下所示 

example1
年度	月	周	姓名
2017	1月	wk1	x
			y
example2
年	月	周	姓名
2017	1月	wk1	x
		wk2
example3
年度	月	周	姓名
2017	1月	wk1	x
	二月	wk2	y

Sheet2是包含所有数据的源数据，如下所示

< td height ="20"style ="height：15pt"> y





















< td>

Name	Month	年度	周	日期	日	活动
x	1月	2017	wk1	1	m	act1，act2
y	二月	2017	wk2	1	t	act1，act2
x	3月	2017	wk3
y	April	2018	wk4
x	可能	2017	wk5
June	2017	wk1
x	July	2018	wk2
y	8月	2018	wk3
y	January	2017	wk1	1	m	act1 ，act2
X	1月	2017	wk2	1	m	act1，act2

解决方案

嗨5Ant，

看来你想根据Sheet1中的数据动态设置Marr / Warr / Rarr，对吧？

您可以尝试获取每列的最后一行索引，并使用行索引将Sheet1中的数据放入数组中。

这是只需代码。

 Sub Test（）
 Dim ws1 As Worksheet 
 Dim ws2 As Worksheet 
 Set ws1 = Sheets（" Sheet1"）
设置ws2 =表格（&quo t; Sheet2"）
 Dim Yarr，Marr，Warr，Rarr As Variant 
 iMonth = ws1.Cells（ws1.Rows.Count，2）.End（xlUp）.Row 
 iWeek = ws1.Cells（ws1.Rows.Count，3）.End（xlUp）.Row 
 iName = ws1.Cells（ws1.Rows.Count，4）.End（xlUp）.Row 
 Marr = Application.Transpose（ws1.Range（QUOT; B2：B" &安培; iMonth）.Value）
 Warr = Application.Transpose（ws1.Range（" C2：C& iWeek）.Value）
 Rarr = Application.Transpose（ws1.Range（" D2： D"& iName）。值）
如果ws2.AutoFilterMode那么
 ws2.AutoFilter.ShowAllData 
结束如果
范围（" B1"）。AutoFilter字段：= 2 ，Criteria1：= Marr，运算符：= xlFilterValues 
范围（"D1"）。AutoFilter字段：= 4，Criteria1：= Warr，运算符：= xlFilterValues 
范围（"A1"）。AutoFilter字段：= 1，Criteria1：= Rarr，运算符：= xlFilterValues 
 End Sub 

最好的问候，

Terry

hi,

Dim Yarr, Marr, Warr, Rarr As Variant

'sheet1 contains below information,pick the value and filter in sheet2

Marr = Array("January") 'i want this to be dynamic range
Warr = Array("wk1")
Rarr = Array("x","y")

'above filter should be applied to sheet2

Range("B1").AutoFilter Field:=2, Criteria1:=Marr, Operator:=xlFilterValues
Range("D1").AutoFilter Field:=4, Criteria1:=Warr, Operator:=xlFilterValues
Range("A1").AutoFilter Field:=1, Criteria1:=Rarr, Operator:=xlFilterValues

i need excel macro to filter sheet2 based on sheet1 criteria, below are the few main points to be considered

-the macro should make sheet1 all the column to dynamic range because the range will vary based on user selection from list box 

as shown in example2, the month range extended with two rows January and February, so next time the user may select all the 7 months so we have to dynamic the range of Month, Week and Name  

-the macro should filter based on sheet1 starting from Column1 to Column4 one after the other

as shown in example1 the macro should first pick Year and filter for 2017 and second pick Month filter for January and third pick Week and filter for wk1 and fourth pick Name and filter for x,y (we can use ubound but i am not good at array)

sheet1 is the value pick the data from list box and it various as shown below 

example1
Year	Month	Week	Name
2017	January	wk1	x
			y
example2
Year	Month	Week	Name
2017	January	wk1	x
		wk2
example3
Year	Month	Week	Name
2017	January	wk1	x
	February	wk2	y

Sheet2 is the sources data which contains all the data, as shown below

Name	Month	Year	Week	Date	Day	Activity
x	January	2017	wk1	1	m	act1,act2
y	February	2017	wk2	1	t	act1,act2
x	March	2017	wk3
y	April	2018	wk4
x	May	2017	wk5
y	June	2017	wk1
x	July	2018	wk2
y	August	2018	wk3
y	January	2017	wk1	1	m	act1,act2
X	January	2017	wk2	1	m	act1,act2

解决方案

Hi 5Ant,

It seems that you want to set the Marr/Warr/Rarr dynamically according to data in Sheet1, right?

You could try to get last row index for each column and the use the row index to put data from Sheet1 to the arrays as you want.

Here is the simply code.

Sub Test()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
Dim Yarr, Marr, Warr, Rarr As Variant
iMonth = ws1.Cells(ws1.Rows.Count, 2).End(xlUp).Row
iWeek = ws1.Cells(ws1.Rows.Count, 3).End(xlUp).Row
iName = ws1.Cells(ws1.Rows.Count, 4).End(xlUp).Row
Marr = Application.Transpose(ws1.Range("B2:B" & iMonth).Value)
Warr = Application.Transpose(ws1.Range("C2:C" & iWeek).Value)
Rarr = Application.Transpose(ws1.Range("D2:D" & iName).Value)
If ws2.AutoFilterMode Then
ws2.AutoFilter.ShowAllData
End If
Range("B1").AutoFilter Field:=2, Criteria1:=Marr, Operator:=xlFilterValues
Range("D1").AutoFilter Field:=4, Criteria1:=Warr, Operator:=xlFilterValues
Range("A1").AutoFilter Field:=1, Criteria1:=Rarr, Operator:=xlFilterValues
End Sub

Best Regards,

Terry

这篇关于excel宏基于sheet1值进行过滤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！