Excel VBA 在列中查找一系列相同的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13364626/
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
Excel VBA find a range of same values in a column
提问by keeg
I need to write a macro that will find the cell range based on a value. A column will have the same value in a row, I need to find out what is the first and last column that has the same value in a row.
我需要编写一个宏来根据值查找单元格范围。一列在一行中将具有相同的值，我需要找出在一行中具有相同值的第一列和最后一列是什么。
So the macro needs to find that "Jill Cross" range is a4 to a9
所以宏需要找到“Jill Cross”的范围是a4到a9
So far I don't have much, got a way to find the first occurrence of a value
到目前为止，我没有太多，有办法找到一个值的第一次出现
Function GetFirstCell(CellRef As Range)
Dim l As Long
l = Application.WorksheetFunction.Match(CellRef.Value, Range("A1:A10000"), 0)
GetFirstCell = l
End Function
Now I need to loop through the next rows somehow to return the last row of an occurrence
现在我需要以某种方式遍历下一行以返回出现的最后一行
回答by nutsch
If you have your first cell in a sorted list, a countif function will give you the last cell easily.
如果您在排序列表中有第一个单元格，则 countif 函数将轻松为您提供最后一个单元格。
Function GetFirstCell(CellRef As Range) as long
Dim l As Long
l = Application.WorksheetFunction.Match(CellRef.Value, Range("A1:A10000"), 0)
GetFirstCell = l
End Function
function GetLastCell(cellRef as range, lFirstCell as long)
Dim l As Long
l = Application.WorksheetFunction.countif(Range("A1:A10000"), CellRef.Value)
GetLastCell = lFirstCell+l-1
End Function
回答by ApplePie
This will work though it has its limitations (for example if the names aren't sorted and the name you are looking for is separated across multiple places in your range...). Of course you need to replace the ranges with those you want to check and "bob" with whatever name you are looking for. Also, the column is static so you may want to alter that part. This is an array formula so you will need to press [ctrl]+[shift]+[enter] in the formula bar to execute it.
这将起作用，尽管它有其局限性（例如，如果名称未排序并且您要查找的名称在您范围内的多个位置分开......）。当然，您需要将范围替换为您要检查的范围，并使用您要查找的任何名称替换“bob”。此外，该列是静态的，因此您可能需要更改该部分。这是一个数组公式，因此您需要在公式栏中按 [ctrl]+[shift]+[enter] 来执行它。
="A"&MIN(IF(ROW(A1:A6)*(A1:A6="bob")=0, 99999999, ROW(A1:A6)*(A1:A6="bob")))&":A"&MAX(SI(ROW(A1:A6)*(A1:A6="bob")=0, -99999999, ROW(A1:A6)*(A1:A6="bob")))
="A"&MIN(IF(ROW(A1:A6)*(A1:A6="bob")=0, 99999999, ROW(A1:A6)*(A1:A6="bob")))&":A"&MAX(SI(ROW(A1:A6)*(A1:A6="bob")=0, -99999999, ROW(A1:A6)*(A1:A6="bob")))
