I have two rows of data looking like this:
I am trying to return all the position numbers for each ID in an array. I have tried
But it fails whenever the ID I am searching for is not the first in column A. (I did try to sort column A with no luck).
So I have come up with this workaround: instead I would use this formula
positions is a VBA function that return an array of rows that match specified ID. The function
positions is coded so that it return a
Variant. But it seems that the VBA Array is not passing to the formula in excel. When I evaluate the formula,
positions(E2) is equal to 0. (I have checked in VBA, and my array is correctly populated).
So how do I make my formula correctly interpret the VBA array?
UPDATE: Here is my code:
Function positions(idrange As Range) As Variant Dim V As Variant Dim l, nb As Integer Dim id As Double nb = 4 ReDim V(nb) As Variant id = idrange.Value Set cible = Sheet2.Range("B1") For l = 1 To nb Set cible = Sheet2.Columns(2).Find(What:=id, After:=cible, _ LookIn:=xlValues) V(l) = cible.Row - 1 Next l positions = Application.Transpose(V) End Function
UPDATE 2: Here is the desired output