Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite
1

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

I want to develop a function that can calculate out the values in the light-green area

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

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

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
share|improve this question
which range in your sheet you want to use as a function Range parameter? which is best for you? – KazJaw Jul 11 at 7:58
2  
Why dont you just use a formula? – brettdj Jul 11 at 8:01
@brettdj I don't know what formula can achieve this. – Aw Qirui Guo Jul 12 at 3:35
add comment (requires an account with 50 reputation)

3 Answers

up vote 0 down vote 
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
share|improve this answer
add comment (requires an account with 50 reputation)
up vote 0 down vote

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.

However, v is 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 v is referring; which will be the column containing the corresponding date.

share|improve this answer
Thanks! I am familiar with JavaScript but very unfamiliar with VBA. Is there anything like for (var i = 0; i<arr.length; i++) in VBA for this case? Thanks! – Aw Qirui Guo Jul 12 at 3:40
Sadly not in the case. A range is not necessarily a vector or even a contiguous block. – Bathsheba Jul 12 at 6:07
add comment (requires an account with 50 reputation)
up vote 2 down vote

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

share|improve this answer
+1 for an answer that doesn't need VBA. But note well that INDIRECT is a volatile function. – Bathsheba Jul 11 at 9:29
Okay, thanks for your advice. Do you know any better solution or replacement for the INDIRECT function in this case? – a.ha Jul 11 at 9:39
what is a "volatile function" - I've never heard that concept before. I searched but found most of the results are C/C++ related stuff. – Aw Qirui Guo Jul 12 at 3:39
Loosely speaking, a volatile function is one which, given that its outputs are not a deterministic function of its inputs, has to be recalculated each time the workbook is calculated. Today and rand are two familiar examples. – Bathsheba Jul 12 at 6:06
add comment (requires an account with 50 reputation)

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.