如何在 VBA For Each 循环中获取索引（使用 Excel 编程）？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17587837/
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
How do I get the index in a VBA For Each Loop (programming with Excel)?
提问by AGamePlayer
I am working with EXCEL VBA to process some data and here is what I want to make:
我正在使用 EXCEL VBA 来处理一些数据，这是我想要做的：
In this sheet, I want to create a function "GetDebutDate" that can automatically calculate the first date that the row has a value.
在此工作表中，我想创建一个函数“GetDebutDate”，该函数可以自动计算该行具有值的第一个日期。
For example, in the row of "Mark", the first time to get a value is Aug-05 with a number of "4".
例如，在“Mark”这一行中，第一次取值是Aug-05，数字为“4”。
I know few about VBA and after searching around I found I can use For ... Eachto loop in a range. But how do I get the index of the "v" in each loop? After I get that index, how do I get the value of the Date in the head row?
我对 VBA 知之甚少，在四处搜索后，我发现我可以For ... Each在一个范围内循环使用。但是如何获得每个循环中“v”的索引？获得该索引后，如何获得标题行中的日期值？
Function Get_Board_Count(range As range)
For Each v In range
....
Next
Get_Board_Count = ....
End Function
采纳答案by a.ha
You can alternatively use an Excel-Only solution, which doesn't need VBA programming. Have a look a this formula:
您也可以使用 Excel-Only 解决方案，它不需要 VBA 编程。看看这个公式：
=INDIRECT(ADDRESS(1,MATCH(TRUE,INDEX(A2:E2>0,0),0)))
MATCH(TRUE,INDEX(A2:E2>0,0),0)looks up the first value which is greater than 0 and not null and returns the current column index of this value. the rest of the formula refers to the row, with the dates, to get the wanted date per reference. In my case is it the first row...
MATCH(TRUE,INDEX(A2:E2>0,0),0)查找大于 0 且不为 null 的第一个值，并返回该值的当前列索引。公式的其余部分引用带有日期的行，以获取每个参考所需的日期。就我而言，它是第一行...
回答by shahkalpesh
Function Get_Board_Count(range As range)
dim i as integer
i = 1
for each v in Range
...
i = i + 1
next
Get_Board_Count = i 'Index of the item where cell is found.
End Function
回答by Bathsheba
When using For Each, it's not a good idea to make any assumptions about the order in which you get the objects: it might change in subsequent Excel versions or even from session to session.
使用 时For Each，对获取对象的顺序进行任何假设并不是一个好主意：它可能会在后续 Excel 版本中甚至从会话到会话中发生变化。
However, vis a reference to a range itself, so you have access to the properties and methods of Excel.Range via it. Use v.Column to extract the column to which a particular vis referring; which will be the column containing the corresponding date.
但是，v是对范围本身的引用，因此您可以通过它访问 Excel.Range 的属性和方法。使用 v.Column 提取特定v引用的列；这将是包含相应日期的列。
