如何在VBA中为每个循环获取索引(使用Excel编程)? [英] How do I get the index in a VBA For Each Loop (programming with Excel)?

问题描述

我正在使用EXCEL VBA处理一些数据，这就是我要制作的:

I am working with EXCEL VBA to process some data and here is what I want to make:

在此工作表中，我想创建一个函数"GetDebutDate"，该函数可以自动计算该行具有值的第一个日期.

In this sheet, I want to create a function "GetDebutDate" that can automatically calculate the first date that the row has a value.

例如，在标记"行中，第一次获取值的时间是08年8月，数字为"4".

For example, in the row of "Mark", the first time to get a value is Aug-05 with a number of "4".

我对VBA知之甚少，经过搜索后发现，我可以使用 For ... Each 在范围内循环.但是，如何获取每个循环中"v"的索引?得到该索引后，如何获得第一行中的Date值?

I know few about VBA and after searching around I found I can use For ... Each to 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?

Function Get_Board_Count(range As range)
  For Each v In range
    ....
  Next
  Get_Board_Count = ....
End Function

推荐答案

您也可以使用仅Excel的解决方案，该解决方案不需要VBA编程.看看这个公式:

You can alternatively use an Excel-Only solution, which doesn't need VBA programming. Have a look a this formula:

=INDIRECT(ADDRESS(1,MATCH(TRUE,INDEX(A2:E2>0,0),0)))

MATCH(TRUE，INDEX(A2:E2> 0,0)，0)查找大于0且不为null的第一个值，并返回该值的当前列索引.公式的其余部分引用带有日期的行，以获取每个引用的所需日期.就我而言，它是第一行...

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...

这篇关于如何在VBA中为每个循环获取索引(使用Excel编程)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！