vba 获取所有符合条件的行并显示在新工作表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16856262/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Fetch all rows that meet criteria and display in new sheet
提问by Smudger
I am trying to automate excel so that it allows me to show all records from another tab where a certain condition is met.
我正在尝试自动化 excel，以便它允许我显示满足特定条件的另一个选项卡中的所有记录。
my source table is(Analysis tab):
我的源表是（分析选项卡）：
I have name all my ranges according to the column name. so for example named range Vehicleis
=Analysis!$A$2:$A$3000
我根据列名命名了我的所有范围。所以例如命名范围Vehicle是
=Analysis!$A$2:$A$3000
all ranges go to row 3000.
所有范围都转到第 3000 行。
dropdown is cell C1, the name of the vehicle and the filter to use.
下拉列表是单元格 C1、车辆名称和要使用的过滤器。
My destination worksheet is as follows:
我的目标工作表如下：
In Cell B3, I have the following array formula that I have tried to adapt unsuccessfully.
=IF(COUNTIF(vehicle,DropDown)<ROWS($A$1:$A1),"",INDEX(DataTable,LARGE(IF(DropDown=Litres,ROW(INDIRECT("1:"&ROWS(vehicle)))),ROW(Analysis!$A1)),MATCH(Analysis!A$3,Analysis!$A$3:$E$3,0)))
在单元格 B3 中，我尝试了以下数组公式，但未成功调整。
=IF(COUNTIF(vehicle,DropDown)<ROWS($A$1:$A1),"",INDEX(DataTable,LARGE(IF(DropDown=Litres,ROW(INDIRECT("1:"&ROWS(vehicle)))),ROW(Analysis!$A1)),MATCH(Analysis!A$3,Analysis!$A$3:$E$3,0)))
This does not work correctly. Any help is appreciated.
这不能正常工作。任何帮助表示赞赏。
to summarize, I want to return all rows from Tab analysis where vehicle is equal to cell C1. I need to automate this as data changes each day.
总而言之，我想从 Tab 分析中返回所有行，其中车辆等于单元格 C1。随着数据每天都在变化，我需要自动执行此操作。
UPDATE
更新
采纳答案by neelsg
You will need to use VBA. A good way to do this is by adding a custom function and then using it in another cell. For instance in cell D1 put =MyFunction(C1). Then create a module in VBA and add the following (May contain some bugs cause I didn't test it):
您将需要使用 VBA。一个很好的方法是添加一个自定义函数，然后在另一个单元格中使用它。例如在单元格 D1 put 中=MyFunction(C1)。然后在 VBA 中创建一个模块并添加以下内容（可能包含一些错误，因为我没有测试它）：
Function MyFunction(parVal As String) As String
'Clear what is there now
ActiveSheet.Range("A3", "I3000").ClearContents
'Add new rows
varRange = Sheets("Analysis").UsedRange
varCount = 2
For varRow = 1 To varRange.Rows.Count
If varRange(varRow, 1) = parVal Then
varRange(varRow, 1).EntireRow.Copy
varCount = varCount + 1
ActiveSheet.Cells(varCount, 1).EntireRow.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End If
Next varRow
MyFunction = "Found " & (varCount - 2) & " rows"
End Function
回答by Francisco
Additional info from: http://www.cpearson.com
附加信息来自：http: //www.cpearson.com
Where To Put The Code
代码放在哪里
The code for a UDF should be placed in a standard code module, not one of the Sheet modules and not in the ThisWorkbook module. In the VBA editor, go to the Insert menu and choose Module. This will insert a new code module into the project. A module can contain any number functions, so you can put many functions into a single code module. You can change the name of a module from Module1 to something more meaningful by pressing the F4 key to display the Properties window and changing the Name property to whatever you want.
UDF 的代码应该放在标准代码模块中，而不是 Sheet 模块之一，也不是 ThisWorkbook 模块中。在 VBA 编辑器中，转到“插入”菜单并选择“模块”。这将在项目中插入一个新的代码模块。一个模块可以包含任意数量的函数，因此您可以将多个函数放入单个代码模块中。您可以通过按 F4 键显示“属性”窗口并将“名称”属性更改为您想要的任何名称，将模块的名称从 Module1 更改为更有意义的名称。
You can call a function from the same workbook by using just the function name. For example:
您可以仅使用函数名称从同一工作簿调用函数。例如：
=RectangleArea(12,34)
=矩形区域（12,34）
