Passing a VBA array to a formula

Question

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

={IF(A2:A562=E2,B2:B562)}

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

={INDEX(B2:B562,positions(E2))}

where 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

@ScottCraner, say I am looking at the first ID. I would like it to return {3,5,7} — jeake
– jeake, Commented Jan 5, 2017 at 17:17
see my answer here: stackoverflow.com/questions/37923950/… — Scott Craner
– Scott Craner, Commented Jan 5, 2017 at 17:24

Scott Craner · Accepted Answer · 2017-01-05 18:18:47Z

3

Put this array formula in F2:

=IFERROR(INDEX($B$2:$B$562,MATCH(1,($A$2:$A$562=$E2)*(COUNTIF($E$2:E2,$B$2:$B$562)=0),0)),"")

Confirm with Ctrl-Shift-Enter instead of Enter. If done correctly then Excel will put {} around the formula.

Then copy over and down sufficient to cover all the data.

EDIT #1

If you can sort the data then you can avoid the array formula and use this normal formula:

=IF(COLUMN(A:A) <= COUNTIF($A:$A,$E2),INDEX($B:$B,MATCH($E2,$A:$A,0)+COLUMN(A:A)-1),"")

edited Jan 5, 2017 at 18:18

answered Jan 5, 2017 at 18:07

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

jeake Over a year ago

One more thing: what if two ID could assume the same position?

Scott Craner Over a year ago

Not sure I understand. Are you saying that the cell has two ID's?

jeake Over a year ago

Let say A values (in column B) are 1,1,7,10

Scott Craner Over a year ago

You now have moved into vba territory. I would ask a new question with the added data and expected outcome. I doubt that a formula will be able to do what you want.

jeake Over a year ago

OK, I'll start a new question

Community · Accepted Answer · 2017-05-23 12:17:05Z

Drawing on Is it possible to fill an array with row numbers which match a certain criteria without looping?, you can do this with array and VBA as so:

This line Filter(Application.Transpose(Application.Evaluate("=IF(A2:A100=OFFSET(E2," & lngCnt - 1 & ",0), (B2:B100),""x"")")), "x", False) returns a string of the matching B values

"1","4","7","10" for A2

This line [e2].Offset(lngCnt - 1, 1).Resize(1, UBound(x) + 1) = Split(Join(x, "|"), "|") puts the string to each array

code

Sub GetEm()

Dim lngCnt As Long
'range of your codes from E2 down
y = [E2:E4]

For lngCnt = 1 To UBound(y)
     x = Filter(Application.Transpose(Application.Evaluate("=IF(A2:A100=OFFSET(E2," & lngCnt - 1 & ",0), (B2:B100),""x"")")), "x", False)
    [e2].Offset(lngCnt - 1, 1).Resize(1, UBound(x) + 1) = Split(Join(x, "|"), "|")
Next
End Sub

Collectives™ on Stack Overflow

Passing a VBA array to a formula

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related