Loop through an array

breakitdown

New Member
Joined
Sep 30, 2006
Messages
2
I have a single column arrary about 16 cells or so, say A1:A16. There are numerical values in each cell. I need either a vba procedure or user defined function that will return the position in the array once a sum is reached. For example if the values were (2,4,5,6,3,4) and my goal was to find "11" i want the procedure to return 3 which is the position in the array where 11 is reached

this is urgent, please help
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
A user defined function

Code:
Function fnd(aRange As Range, Target)
Dim tot, i, cel As Range
tot = 0
fnd = 0
i = 0
For Each cel In aRange
i = i + 1
tot = tot + cel.Value
If tot = Target Then
fnd = i
Exit Function
End If
Next cel
End Function

example: =fnd(A1:A6,11)
 

Watch MrExcel Video

Forum statistics

Threads
1,108,501
Messages
5,523,293
Members
409,508
Latest member
Afc

This Week's Hot Topics

Top