Help with array loop

[288enzo]

I have a formula that I'm trying to loop through an array in cell B4, E4, and H4.

I haven't tried doing this with an FormulaArray before, and I think it might be throwing me off.

This is what I have:

Dim i As Long
Dim rng As Range
Dim var As Variant

var = Array("""SA""", """SV""", """PA""")
Set rng = Range("B4")

For i = 0 To 6

rng.Offset(0, i + 3).FormulaArray = "=SUM(--(MMULT(--IF((Employees!$C$4:$C$3000=RIGHT(A4,3))*(Employees!$H$4:$H$3000=" & dept & "),(Employees!$K$4:$N$3000=""N""),0),TRANSPOSE(COLUMN(Employees!$K$4:$N$3000)^0))>0))"

Next i

I'm getting "Unable to set the FormulaArray property of the Range class".

 

[Fluff]

What is dept meant to be?

 

[288enzo]

What is dept meant to be?

For a second I thought that was the fix. "dept" should be replaced with "var".

 

[Fluff]

Are you trying to put the formula into B4 with SA, E4 with SV etc?

 

[288enzo]

Are you trying to put the formula into B4 with SA, E4 with SV etc?

Yes, and it makes me sad you have to ask. I have so much to learn.

 

[Fluff]

Ok, try it like

Dim i As Long
Dim rng As Range
Dim var As Variant

var = Array("""SA""", """SV""", """PA""")
Set rng = Range("B4")

For i = 0 To UBound(var)

rng.Offset(0, i * 3).FormulaArray = "=SUM(--(MMULT(--IF((Employees!$C$4:$C$3000=RIGHT(A4,3))*(Employees!$H$4:$H$3000=" & var(i) & "),(Employees!$K$4:$N$3000=""N""),0),TRANSPOSE(COLUMN(Employees!$K$4:$N$3000)^0))>0))"

Next i

 

[288enzo]

Ok, try it like

Dim i As Long
Dim rng As Range
Dim var As Variant

var = Array("""SA""", """SV""", """PA""")
Set rng = Range("B4")

For i = 0 To UBound(var)

rng.Offset(0, i * 3).FormulaArray = "=SUM(--(MMULT(--IF((Employees!$C$4:$C$3000=RIGHT(A4,3))*(Employees!$H$4:$H$3000=" & var(i) & "),(Employees!$K$4:$N$3000=""N""),0),TRANSPOSE(COLUMN(Employees!$K$4:$N$3000)^0))>0))"

Next i

Of course that worked. Can I ask, why the difference with rng.Offset(0, i * 3) vs rng.Offset(0, i + 3)? In my mind I saw B4 + 3 columns, I don't understand the use of "*".

Thank you

 

[Fluff]

When using + you are adding 3 to i, so the formula will go into E4, F4 & G4, but using * multiplies i by 3 so the formula goes into B4, E4 & H4