VBA 365 functions

[Ezguy4u]

So I just got Excel 365 and took a short class on the new 365 functions. I am going to use Filter and Unique. I don't know why but in cell C7 the formula is actually =@UNIQUE(A2:A17) when I ran the program. So the question is how do I get that unique formula in C7 without getting that implicit intersection @?
Thank you for your time

Sub Prog02()

Range("C7").Formula = "=UNIQUE(A2:A17)"

End Sub

new 365 functions using VBA rev a.xlsm
	A	B	C
1	Division		Division
2	Utility		Utility
3	Utility		Productivity
4	Utility		Game
5	Utility		Health
6	Utility
7	Productivity		Utility
8	Productivity
9	Productivity
10	Productivity
11	Productivity
12	Game
13	Game
14	Game
15	Game
16	Game
17	Health
UNIQUE

Cell Formulas
Range	Formula
C2:C5	C2	=UNIQUE(A2:A17)
C7	C7	=UNIQUE(A2:A17)
Dynamic array formulas.

I will add filter to the problem in hopes of the solution will be for both unique and filter. Again =@FILTER(A6:C20,C6:C20>G11) is in cell E11

Sub Prog05A()

Range("E11").Formula = ("=FILTER(A6:C20,C6:C20>G11)")

End Sub

new 365 functions using VBA rev a.xlsm
	A	B	C	D	E	F	G
1	Division	Region	Revenue
2	Utility	North America	44,196
3	Utility	South America	20,898			Revenue greater than:	45,000
4	Utility	Asia	46,994
5	Utility	Europe	43,695		Utility	Asia	46994
6	Utility	Australia	34,196		Productivity	Europe	45540
7	Productivity	North America	34,155		Game	Europe	46336
8	Productivity	South America	24,396		Game	Australia	49656
9	Productivity	Asia	29,276
10	Productivity	Europe	45,540
11	Productivity	Australia	29,277		Utility
12	Game	North America	44,675
13	Game	South America	42,569
14	Game	Asia	43,784
15	Game	Europe	46,336
16	Game	Australia	49,656
FILTER

Cell Formulas
Range	Formula
E5:G8	E5	=FILTER(A2:C16,C2:C16>G3)
E11	E11	=FILTER(A6:C20,C6:C20>G11)
Dynamic array formulas.

 

[Peter_SSs]

In your codes, instead of .Formula use .Formula2

You can do more research but here is a start.

Range.Formula2 property (Excel)

Office VBA reference topic

learn.microsoft.com

 

[Ezguy4u]

And it is just that easy. Thank you Peter. I will look at that link and understand it better.

 

[Peter_SSs]

You're welcome. Good luck with the transition to 365!