Formula Array - Nomenclature

Hi,

I'm new to VBA and need a little help.

I'm trying to put the below Formula Array in a cell using VBA but I get an "Run-Time Error 1004 Application-Defined or object-defined error"

=IFERROR(INDEX(Meals,(SMALL(IF(Meals[Meal]=$A$1,ROW(Meals[Meal])),ROW(1:1)))-1,2),"")

When I put the formula in the cell and press CTRL + SHIFT + ENTER , it works fine though.

I'm not really sure why this is happening, I'm guessing it has to do with the Table Meals? Meals is a table in another worksheet but same Workbook.

This is the code.

Sub MC()

Worksheets("Meal Calculator").Range("A1").FormulaArray = "=IFERROR(INDEX(Meals,(SMALL(IF(Meals[Meal]=$A$1,ROW(Meals[Meal])),ROW(1:1)))-1,2),"")"


End Sub

Wow, I can't believe that...

Can you help me a little further? I can't quiet figure out how to get the second Integer "XXX" to loop with the first Integer "RowA".

The loop works fine for the rowA, working its way down the list. But I can't quiet figure out how to get the Row function going from Row(1:1) to Row(20:20). Thank you in advance

Sub MC()

Dim rowA As Integer
Dim XXX As Integer

For rowA = 4 To 24 & XXX = 1 to 20


Worksheets("Meal Calculator").Range("A" & rowA).FormulaArray = "=IFERROR(INDEX(Meals,(SMALL(IF(Meals[Meal]=$A$1,ROW(Meals[Meal])),ROW(XXX:XXX)))-1,2),"""")"

Worksheets("Meal Calculator").Range("B" & rowA).FormulaArray = "=IFERROR(INDEX(Meals,(SMALL(IF(Meals[Meal]=$A$1,ROW(Meals[Meal])),ROW(XXX:XXX)))-1,3),"""")"

Next rowA & XXX

End Sub

I can't believe that, I'm going to have to buy a book. Learning vba for dummies.

Originally Posted by AlphaFrog

You're welcome.

I'm driving right now I haven't checked if it works I'm pretty sure it does though thank you a lot I spent like 2 hours on that