Excel formula won't work in VBA

[BradleyS]

I have the following formula which checks the dates in column B are in a date period and then counts the unique numbers in column A and works fine.

Range("H8").Select
=SUMPRODUCT(IF((B2:B5<=J5)*(B2:B5>=I5), 1/COUNTIFS(B2:B5, "<="&J5, B2:B5, ">="&I5, A2:A5, A2:A5), 0))

However, I need to add another bit of criteria, which is to ignore any lines that have the words "NO DAY MATCH" in some of the cells in column E, so I have added this:
,--(E2:E5<>"NO DAY MATCH")

The full formula now looks like this. (Ctrl+Shift+Enter)
=SUMPRODUCT(IF((B2:B5<=J5)*(B2:B5>=I5), 1/COUNTIFS(B2:B5, "<="&J5, B2:B5, ">="&I5, A2:A5, A2:A5), 0),--(E2:E5<>"NO DAY MATCH"))"

However, it only works when entered manually into the Excel formula bar. When I use VBA it doesn't show anything in the cell and I can't work out why.

Selection.FormulaArray = "=SUMPRODUCT(IF((B2:B" & LR & "<=J5)*(B2:B" & LR & ">=I5), 1/COUNTIFS(B2:B" & LR & ", ""<=""&J5, B2:B" & LR & ", "">=""&I5, A2:A" & LR & ", A2:A" & LR & "), 0),--(E2:E" & LR & "<>""NO DAY MATCH""))"

I have even run a macro based on entering the formula, which gives me the code below but when I re-run the code it is still blank.

Range("H8").Select
Selection.FormulaArray = _
"=SUMPRODUCT(IF((R[-6]C[-6]:R[-3]C[-6]<=R[-3]C[2])*(R[-6]C[-6]:R[-3]C[-6]>=R[-3]C[1]), 1/COUNTIFS(R[-6]C[-6]:R[-3]C[-6], ""<=""&R[-3]C[2], R[-6]C[-6]:R[-3]C[-6], "">=""&R[-3]C[1], R[-6]C[-7]:R[-3]C[-7], R[-6]C[-7]:R[-3]C[-7]), 0),--(R[-6]C[-3]:R[-3]C[-3]<>""NO DAY MATCH""))"

Why does it only work if I enter the formula manually into Excel?
Is my formula wrong?

Table of data if it helps

Excel 2010
	A	B	C	D	E
1	id	eventdate	code	read_term	CHECK
2	5105	01/10/2018	9N	text	7G1
3	7920	01/04/2018	9N	text	8BC
4	19714	31/12/2018	9N	text	NO DAY MATCH
5	19714	01/04/2018	9N	text	NO DAY MATCH
MS1

 

[shg]

If instead of putting the formula in the cell, you assign it to a string variable and print it to the Immediate window, you should be able to copy it from there and paste it into the cell. Assuming that doesn't work, you can figure out why and fix it.

 

[joeu2004]

When I enter the statement as Range("H8").FormulaR1C1=... , it works "just fine", except that the formula returns #VALUE in Excel because it is not array-entered. When I manually edit H8 (press f2) and array-enter the formula, it returns the same value (zero) as when I array-enter the equivalent Excel formula in your posting.

So I think the problem is: the property FormulaArray does not understand R1C1 notation; and there does not seem to be an equivalent FormulaArrayR1C1.

Why are you using R1C1 notation?

PS.... On the other hand, a google search found a similar question from 6 years ago, and the answer then was: the FormulaArray worked, notwithstanding the R1C1 notation; the user's only problem was a syntax error in the formula text in VBA. But again, that does not seem to be an issue with your formula text.

PPS.... I suspect you are leveraging VBA code that was produced in a recorded macro. I have encountered situations where the recorded macro text does not work.

 

[BradleyS]

I am using FormulaArray in my VBA code, it's only when I used the macro to record my actions, that it wrote it in R1C1 but as you say it still doesn't apply the R1C1 to the array.

Is there another way to write my formula, to produce the desired effect of ignoring rows with that text and checking between a date range?
I was trying to use it with Sum(IF or countif but I can't get it to work using these.

I will also try what shg says.

 

[joeu2004]

First, I thought the following should work:

With Range("H8")
    On Error Resume Next
    .FormulaR1C1 = _
        "=SUMPRODUCT(IF((R[-6]C[-6]:R[-3]C[-6]<=R[-3]C[2])*(R[-6]C[-6]:R[-3]C[-6]>=R[-3]C[1]), 1/COUNTIFS(R[-6]C[-6]:R[-3]C[-6], ""<=""&R[-3]C[2], R[-6]C[-6]:R[-3]C[-6], "">=""&R[-3]C[1], R[-6]C[-7]:R[-3]C[-7], R[-6]C[-7]:R[-3]C[-7]), 0),--(R[-6]C[-3]:R[-3]C[-3]<>""NO DAY MATCH""))"
    On Error GoTo 0
    .FormulaArray = .Formula
End With

But it doesn't! Aha! I think the failure of above is explained by the inexplicable error below:

Range("H8").FormulaArray = _
"=SUMPRODUCT(IF((B2:B5<=J5)*(B2:B5>=I5), 1/COUNTIFS(B2:B5, ""<=""&J5, B2:B5, "">=""&I5, A2:A5, A2:A5), 0),--(E2:E5<>""NO DAY MATCH""))"

Yet, if I change the property to Formula and let the #VALUE error happen, then manually edit H8 (press f2) and array-enter, the array-entered formula "works" insofar as it returns zero.

So I'm out of ideas. Sorry.

 

[BradleyS]

I tried to paste from a formula using the following. I have to use FormulaArray as that is the only way I know how to add Ctrl+Shift+Enter

LR = 5
Range("H8").FormulaArray = "=SUMPRODUCT(IF((B2:B" & LR & "<=J5)*(B2:B" & LR & ">=I5), 1/COUNTIFS(B2:B" & LR & ", ""<=""&J5, B2:B" & LR & ", "">=""&I5, A2:A" & LR & ", A2:A" & LR & "), 0))"


the above code works which we know, but as soon as I add: ,--(E2:E" & LR & "<>""NO DAY MATCH"")
to the end of the code

i.e. full code:
Range("H8").FormulaArray = "=SUMPRODUCT(IF((B2:B" & LR & "<=J5)*(B2:B" & LR & ">=I5), 1/COUNTIFS(B2:B" & LR & ", ""<=""&J5, B2:B" & LR & ", "">=""&I5, A2:A" & LR & ", A2:A" & LR & "), 0),--(E2:E" & LR & "<>""NO DAY MATCH""))"

I get this error message

Run-time error '1004':
"Unable to set the FormulaArray property of the range class"


What is it about this last section of code that stops it from working in VBA?

 

[BradleyS]

Sorry it returns zero because I forgot to add the 2 dates it is looking between in I5 = 01/10/2018 and J5 = 31/12/2018

Not that this helps resolve the error

 

[Marcelo Branco]

I think this should work - i used a different (more efficient) formula to get the unique counting.

Sub Test()
    Dim LR As Long
    
    LR = Cells(Rows.Count, "A").End(xlUp).Row
    Range("H8").FormulaArray = Replace("=SUM(IF(FREQUENCY(IF(B2:B@>=I5,IF(B2:B@<=J5,IF(E2:E@<>""NO DAY MATCH""," _
        & "MATCH(A2:A@,A2:A@,0)))),ROW(A2:A@)-ROW(A2)+1),1))", "@", LR)
End Sub

M.

 

[BradleyS]

YOU IS THE MAN!!!!

I have been working on this nearly all day and finally came to the forum and was just about to give it up, when you answered my prays!!!

Thank you so much.

 

[Marcelo Branco]

You're welcome. Glad to help

M.