VBA FormulaARrray (Index w/ Multiple criteria)

[acerlaptop]

Hi,

I have a part here of my VBA code. I don't know what the problem is but the code returns a single result to all the covered ranges. It only returns the result for the first item.

For example:
In column F2 up to F10
All the results returned by the code from F2 to F10 is the result for F2.

Sheets(strN3).Range("F2:F" & LastRow2).FormulaArray = _
    "=INDEX(HELPER!C[-3],MATCH(1,(HELPER!C[-2]=RC[-3])*(HELPER!C[-1]=RC[-2]),0))"

Any thoughts?

Thanks

 

[Fluff]

How about

Sheets(strN3).Range("F2").FormulaArray = _
    "=INDEX(HELPER!C[-3],MATCH(1,(HELPER!C[-2]=RC[-3])*(HELPER!C[-1]=RC[-2]),0))"
Sheets(strN3).Range("F2:F" & lastrow2).FillDown

 

[acerlaptop]

A

How about

Sheets(strN3).Range("F2").FormulaArray = _
    "=INDEX(HELPER!C[-3],MATCH(1,(HELPER!C[-2]=RC[-3])*(HELPER!C[-1]=RC[-2]),0))"
Sheets(strN3).Range("F2:F" & lastrow2).FillDown

And it's a miracle

Thanks a lot. I've been having problem with that part. Anyway, why does my approach works with FormulaR1C1 but not with FormulaArray?

 

[Fluff]

No idea, I just know that you can't directly populate a range with an array formula, but have to put it in one cell & than fill down.
The same way as you if doing it manually.

 

[acerlaptop]

No idea, I just know that you can't directly populate a range with an array formula, but have to put it in one cell & than fill down.
The same way as you if doing it manually.

Okay. Thanks a LOT!!!

 

[Fluff]

You're welcome & thanks for the feedback

 

[acerlaptop]

How about

Sheets(strN3).Range("F2").FormulaArray = _
    "=INDEX(HELPER!C[-3],MATCH(1,(HELPER!C[-2]=RC[-3])*(HELPER!C[-1]=RC[-2]),0))"
Sheets(strN3).Range("F2:F" & lastrow2).FillDown

hI,

Why does this not function when the number of rows is 1?

 

[Fluff]

Because there is nothing to filldown
You can test for it like

Sheets(strN3).Range("F2").FormulaArray = _
    "=INDEX(HELPER!C[-3],MATCH(1,(HELPER!C[-2]=RC[-3])*(HELPER!C[-1]=RC[-2]),0))"
If lastrow2 > 2 Then Sheets(strN3).Range("F2:F" & lastrow2).FillDown

 

[acerlaptop]

Because there is nothing to filldown
You can test for it like

Sheets(strN3).Range("F2").FormulaArray = _
    "=INDEX(HELPER!C[-3],MATCH(1,(HELPER!C[-2]=RC[-3])*(HELPER!C[-1]=RC[-2]),0))"
If lastrow2 > 2 Then Sheets(strN3).Range("F2:F" & lastrow2).FillDown

So that's why. But can I put IF without End If?

 

[Fluff]

If it's a one line If, then yes