FormulaLocal vs Formula2Local issue

Gringoire

Board Regular
Joined
Nov 18, 2016
Messages
71
Office Version
  1. 365
Platform
  1. Windows
Hi folks!
I'm just trying to write a formula in a cell.

if I use Formula2Local I got a '1004' Runtime error.
VBA Code:
Worksheets("SK").Cells(ACT_row, Col_VAL).Formula2Local = Aggr_costi

if I use FormulaLocal everything run smoothly
VBA Code:
Worksheets("SK").Cells(ACT_row, Col_VAL).FormulaLocal = Aggr_costi

The runtime WatchWindow shows that Aggr_costi is a string: : Aggr_costi : "=AGGREGA(9;0;$L$48:$L$52)" : Variant/String :
and the aggregate interval is a Range: Worksheets("SK").Cells(ACT_row, Col_VAL) : Variant/Object/Range

I read this doc about formula and Formula2 and I can't understand why Formula2Local gives an error in this case.
I could go on and use FormulaLocal instead but:

- I prefer avoiding to do things without understanding why
- M$ suggest to use Formula2 as best practice

Could someone help me to understand why Formula2Local does not works in this case?

P.S: AGGREGA is the Italian formula for AGGREGATE. This is why I'm using Formula2Local and not Formula2

thanks
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
(Per evitare equivoci) Please tel us which version of Excel you use

Bye
 
Upvote 0
You can use Formula2 only on Dynamic Array aware Excel versions.
This feature has been broadly released on Jan 2020 to Office 365 subscribers (it had been available to "some" of the Office Inseders program partecipants since Jan 2019).

So you are on a "traditional" environment, you should not use Formula2 except in Conditional formatting

Ciao
 
Upvote 0
Solution

Forum statistics

Threads
1,224,938
Messages
6,181,869
Members
453,068
Latest member
DCD1872

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top