Formula Array Not working with variables

[bendy315]

I'm trying to use a formula array to do some calculations..

this works..
Range("C" & LastRow + 4).Select
Selection.FormulaArray = _
"=PERCENTILE(IF(L2:L" & LastRow & "=""15"",AC2:AC" & LastRow & "),0.4)"

but when I try to replace the 15 with a variable (year) it just returns a zero or #Value
Range("C" & LastRow + 4).Select
Selection.FormulaArray = _
"=PERCENTILE(IF(L2:L" & LastRow & "=" & year & ",AC2:AC" & LastRow & "),0.4)"


help! thanks.

 

[dreid1011]

What type of variable is year?

Your use of 15 in the first version that works is as a string. If year is not a string variable, then that may be the cause of the zero result or error.

 

[Jonmo1]

It's the quotes...

Say LastRow = 10, your first code translates like this
"=PERCENTILE(IF(L2:L" & LastRow & "=""15"",AC2:AC" & LastRow & "),0.4)"
"=PERCENTILE(IF(L2:L" & 10 & "=""15"",AC2:AC" & 10 & "),0.4)"
"=PERCENTILE(IF(L2:L10=""15"",AC2:AC10),0.4)"

Now If
LastRow = 10
Year = 15

Your 2nd code translates like this
"=PERCENTILE(IF(L2:L" & LastRow & "=" & year & ",AC2:AC" & LastRow & "),0.4)"
"=PERCENTILE(IF(L2:L" & 10 & "=" & 15 & ",AC2:AC" & 10 & "),0.4)"
"=PERCENTILE(IF(L2:L10=15,AC2:AC10),0.4)"

Notice the missing double quotes on the 15

Try
"=PERCENTILE(IF(L2:L" & LastRow & "=""" & year & """,AC2:AC" & LastRow & "),0.4)"

 

[bendy315]

year is just an integer variable. yep, it was the quotes. thanks!

 

[bendy315]

trying to use the same syntax for a different array formula in the same code. This one seems to not return anything. just null value in the cell.. can't seem to figure this one out either...

Range("H" & LastRow + 4).Select
Selection.FormulaArray = _
"=COUNTIFS(L2:L" & LastRow & ","" = """ & year & """"",AC2:AC" & LastRow & ","" <= """ & std & """"") / count"

lastrow=6, year =15, std = 700, & count=2. so answer should be 0.5 based on the data.

L AC
Year Hours
15 530
15 1,106
16 80
16 468
17 687

any thoughts...?