Excel 365 - VBA to assign a formula

[JKwan]

I am trying to assign a forumla in Excel 365 using VBA but run into something that I don't quite understand....


ActiveCell.Formula = "=Unique(A3:D" & LastRow & ")" ' I wanted to do this

ActiveCell.Formula = "'=Unique(A3:D" & LastRow & ")" ' at the end I use this then I delete the '


so, the first formula gets put into my cell, but it contains this symbol "@", because of that symbol, I only have one cell. As soon as I delete the symbol, my whole range appears??!! So, I did the second way to avoid that problem and works.
so, why is Excel putting that symbol into my formula and yet, it does not give me what I want.... How does one overcome this quirk to make it work?

[Paul_Hossler]

Last bullet

UNIQUE is a dynamic array command so I think Excel is trying to help you :banghead:

https://www.excelcampus.com/functions/dynamic-array-formulas-spill-ranges/



Dynamic Array Formulas Are Coming!As I mentioned, these functions are not yet available to the general public. The current availability is limited to a portion of users on Microsoft's Office Insiders Program (https://products.office.com/en/office-insider) (Insider channel). The program is free for Office 365 subscribers. There is no set release date to all Office 365 users yet, but hopefully that will be soon.
As of now, there are 7 new dynamic array functions:


Filter (https://support.office.com/en-us/article/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759?ui=en-US&rs=en-001&ad=US) – allows you to filter a range of data based on criteria you define.
RandArray (https://support.office.com/en-us/article/randarray-function-21261e55-3bec-4885-86a6-8b0a47fd4d33?ui=en-US&rs=en-001&ad=US) – returns an array of random numbers.
Sequence (https://support.office.com/en-us/article/sequence-function-57467a98-57e0-4817-9f14-2eb78519ca90?ui=en-US&rs=en-001&ad=US) – allows you to generate a list of sequential numbers in an array, such as 1, 2, 3, 4.
Sort (https://support.office.com/en-us/article/sort-function-22f63bd0-ccc8-492f-953d-c20e8e44b86c?ui=en-US&rs=en-001&ad=US) – sorts the contents of a range or array.
SortBy (https://support.office.com/en-us/article/sortby-function-cd2d7a62-1b93-435c-b561-d6a35134f28f?ui=en-US&rs=en-001&ad=US) – sorts based on the values in a corresponding range or array.
Unique (https://support.office.com/en-us/article/unique-function-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e?ui=en-US&rs=en-001&ad=US) – returns a list of unique values in a list or range.
Single – returns a single value at the intersection of a cell's row or column. Update: The Single function has been removed from Excel and the @ symbol is now used instead for backward compatibility.





There's also the # marker (?) to refer to the entire Spill range



However, Excel's implementation of the spill range and spill refs (A4#) is different (at the time of this writing). It opens up a whole new world of possibility and simplicity with Excel formulas and other features.



Edit --------------------------------------------------------------------------------

Use .Formula2 =


ActiveCell.Formula2 = "=Unique(A3:D" & LastRow & ")" ' I wanted to do this

[JKwan]

Paul:
Thanks, that did the trick!

[Paul_Hossler]

FWIW, I really like the new 365 functions, but not everyone has 365 so I have to do things the old way :crying:

[JKwan]

FWIW, I really like the new 365 functions, but not everyone has 365 so I have to do things the old way :crying:

I like it too.... However, with the "@" sign, I only get one Unique cell, instead of my range that I specified, that I don't like

[Paul_Hossler]

(Guessing again)

I think Excel was trying to be helpful :banghead:


In your line ...


ActiveCell.Formula = "=Unique(A3:D" & LastRow & ")" ' I wanted to do this



... Excel probably assumed only a single cell was required since you used the pre-dynamic array .Formula (no '2')



https://a4accounting.com.au/new-formula-symbol-in-excel/


The extended use symbol @

....
....

Summing up
The # symbol follows the reference and it makes it incredibly easy to refer to spilled ranges. You use the # after the reference to the cell in the top left corner of the spilled range.


The @ symbol precedes the reference and it forces Excel to treat the reference as it would have pre-dynamic arrays.

[JKwan]

Thank you for the link, now I understand and know what to do, Paul.