Vba Formula Array Calc

[bendy315]

Trying to calculate the percentage of projects meeting the target for each year. Here's what I came up with. Maybe there is a better way to do this, but I need it to work dynamically with changing data. What I have so far doesn't seem to return anything, just a null value...

'P-Std Calc
Range("H" & rwc).Select
Selection.FormulaArray = _
"=COUNTIFS(L2:L" & LastRow & ","" = """ & year & """"",AC2:AC" & LastRow & ","" <= """ & std & """"") / & count & "

Here's the Data...
[TABLE="width: 500"]
<tbody>[TR]
[TD]Year[/TD]
[TD]Hrs[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]530[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]1,106[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]486[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]687[/TD]
[/TR]
</tbody>[/TABLE]


In this case year = 15, lastrow = 6, std = 700, and count =2, so should return 50% of projects in '15 beat the target.

 

[JackDanIce]

It's not very clear your ask:

What is the name of the sheet?
What is the start cell address of your data?
What column determines the last (dynamic) row?

How does the formula look if you type it directly into cell H & rwc (ignoring the dynamic changing rows) and what value is rwc?

Assume anyone reading cannot see your PC screen, how do you precisely explain the problem to them so they can re-create it on a dummy workbook?

It may help to post the full code you have so far.

 

[Marcelo Branco]

See if this does what you need

Range("H" & rwc).Formula = _
    "=COUNTIFS(L2:L" & LastRow & ",""=" & Year & """,AC2:AC" & LastRow & ",""<=" & std & """)/" & count & ""

Thoughts
1. No need to Select the range
2. It isn't an array formula, so you can use
Range(...).Formula
3.Year is not an appropriate name for a variable because there is a VBA function with the same name. To avoid any problem change it to something like lYear.

Hope this helps

M.

 

[bendy315]

Thanks Marcelo,

made the suggested changes and now works great.

1. removed 'array' formula
2. changed variable to yr

Range("H" & rwc).Select
ActiveCell.Formula = _
"=COUNTIFS(L2:L" & LastRow & ",""=" & yr & """,AC2:AC" & LastRow & ",""<=" & std & """)/" & count & ""

 

[JackDanIce]

You may find it easier to build a long formula with double quotes and concatenation as:

Sub StrFormula()

    Dim str  As String
    
    str = "=COUNTIFS($L$2:$L$@LR,"="&@YR,$AC$2:$AC$@LR," <= "&@std)/@count"

    str = Replace(strFrm, "@LR", LastRow)
    str = Replace(strFrm, "@YR", yr)
    str = Replace(strFrm, "@std", std)
    str = Replace(strFrm, "@count", count)

    Range("H" & rwc).Formula = str
    
End Sub

 

[Marcelo Branco]

Thanks Marcelo,

made the suggested changes and now works great.

You are welcome. Thanks for the feedback.

M.

 

[Marcelo Branco]

You may find it easier to build a long formula with double quotes and concatenation as:

Sub StrFormula()

    Dim str  As String
    
    str = "=COUNTIFS($L$2:$L$@LR,"="&@YR,$AC$2:$AC$@LR," <= "&@std)/@count"

    str = Replace(strFrm, "@LR", LastRow)
    str = Replace(strFrm, "@YR", yr)
    str = Replace(strFrm, "@std", std)
    str = Replace(strFrm, "@count", count)

    Range("H" & rwc).Formula = str
    
End Sub

Good idea!
Or a Function that receives as arguments LastRow, yr, std and count and returns the strFormula

M.

 

[JackDanIce]

Hi Marcelo lol I had written that part out, before you'd posted your solution, so thought if it helps, include.

Agree, a string function with arguments would work just as well, but based on the posted code, thought a Sub procedure would be easier to understand for OP as an example on using Replace (string substitution) to amend a string vs concatenation to join strings with variables (non strings)

Good idea!

Thank you for the compliment!

 

[Marcelo Branco]

Hi Jack,

Agree, a string function with those variables would work just as well, but based on the posted code, thought within a sub may be easier to understand for the OP as example on how to use substitution (Replace) to amend a string vs concatenation to join strings and non strings.

I didn't test your code but it seems to me that is necessary to double the quotes inside the initial string - not sure, did you test it?

Thank you for the compliment!

Well deserved


M.

 

[JackDanIce]

This is actually what I wrote:

Sub StrFormula()

    Dim str  As String
    
    str = "=COUNTIFS($L$2:$L$@LR,""=""&@YR,$AC$2:$AC$@LR,""<=""&@std)/@count"

    str = Replace(str, "@LR", 10)
    str = Replace(str, "@YR", 2017)
    str = Replace(str, "@std", 0.5)
    str = Replace(str, "@count", 2)

    Cells(1, 1).Formula = str
    
End Sub

And A1 definitely contains a formula that returns 0 on the blank sheet I tested it on.

I undetstand it as if a formula is =xxx&"="yyy when you type it into a cell then with VBA it's the string part inside the black speech marks "=xxx&"="yyy" that is equivalent to what you are typing that is passed to the cell as a formula to evaluate.

So the blue part can be anything because it's inside the speech marks i.e. a pure string which just happens to have speechmark characters inside it.

It's how I find Replace easier to use to avoid the concatenation stuff and I expect I've probably misunderstood some things in above too!