Translating array formula - count unique values between dates on filtered sheet - to VBA

Hi All,

63falcondude was kind enough to assist me with a formula to count unique values between dates on filtered sheet here:https://www.excelforum.com/excel-for...red-sheet.html and I thought I would be able to easily translate the formula into VBA as I have done many times. Unfortunately, this one has got the better of me! The original, working formula looks like this:

{=SUM(IF(FREQUENCY(IF(SUBTOTAL(103,OFFSET(C9,ROW(C9:C15)-ROW(C9),,1)),IF((D9:D15>=D2)*(D9:D15<=D3),MATCH("~"&C9:C15,C9:C15&"",0))),ROW(C9:C15)-ROW(C9)+1),1))}

My best effort to translate to VBA looks like this:

Please Login or Register  to view this content.

When the code gets to here, I get an "Unable to set the FormulaArray property of the Range class" error. I thought it had something to do with the number of quotation marks where quotation marks are required in the formula but no matter what combination I try, I keep getting the error. I thought I knew the syntax for this but clearly I don't or there is another issue I'm missing. I'm using the LastRow + 8 variable in other formulas in the code just above this line that work just fine.

Any help would be appreciated.

Many thanks,

This is from the Remarks section for the .FormulaArray help screen.

Remarks
If you use this property to enter an array formula, the formula must use the R1C1 reference style, not the A1 reference style (see the second example).

Hi AlphaFrog, thanks for the reply.

I'm still confused as I have used the following code in the same macro that works and applies the array formula to the worksheet:

Please Login or Register  to view this content.

Is there a function or character in my query formula that is different that would cause the error? It would be good to know why one line of similar code works and not the other.

Many thanks,

It works if you apply it using just .Formula
So I would guess the syntax is correct.

I don't know the problem is. Perhaps it currently evaluates to an error? Just a another guess.

Odd behavior. When I add as .Formula, exit the macro and manually CTRL + SHIFT + ENTER, the formula converts to an array and works perfectly. I've now even tried converting the formula to an array in a second line:

Please Login or Register  to view this content.

The first line inserts the formula just fine but the second gets the error.

Untitled.png

Does anyone know how I might add this array formula to my worksheet?

Many thanks,

For what it's worth, it sucsessfully appies the FormulaArray using the R1C1 reference style

Have worked this out now. Yes R1C1 reference style does work, I just needed to learn more about it. My understanding of R1C1 is much better now!

Thanks for your assistance AlphaFrog.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Oops. Meant to do that! Thanks AliGW.