How to insert an Array formula in VBA

MHD

New Member
Joined
Mar 16, 2021
Messages
37
Office Version
  1. 2019
Hello everyone

This function works fine and all references and arguments are typed correctly.

Sub Macro4()

Range("C2").FormulaArray = _
"=IF([@Unique]=1,TEXT([@[Date of Visits]],""dd.mm.yyyy""),""(""&TEXTJOIN("","",,IF(([i.Village]=[@[i.Village]])*([Month]=[@Month])*(MATCH([Date of Visits]&[i.Village],[Date of Visits]&[i.Village],0)=MATCH(ROW([Date of Visits]),ROW([Date of Visits]),0)),TEXT([Date of Visits],""dd""),""""))&"");""&TEXT([@[Date of Visits]],""mm.yyyy""))"

End Sub


But I am trying to register a "VBA code" macro that will automatically type it into cell C2; by open the cell C2 and press (ctrl shift enter), but the developer shows an error when running the code.

Please any help
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,319
Office Version
  1. 365
Platform
  1. Windows
The formula is too long, try it like
VBA Code:
Sub Macro4()
   Dim Formula1 As String, Formula2 As String, Formula3 As String
   
   Formula1 = "=IF([@Unique]=1,TEXT([@[Date of Visits]],""dd.mm.yyyy""),""(""&TEXTJOIN("","",,IF(([i.Village]=[@[i.Village]])*([Month]=[@Month])*(XXXX=WWWW),TEXT([Date of Visits],""dd""),""""))&"");""&TEXT([@[Date of Visits]],""mm.yyyy""))"
   Formula2 = "MATCH([Date of Visits]&[i.Village],[Date of Visits]&[i.Village],0)"
   Formula3 = "Match(Row([Date of Visits]), Row([Date of Visits]), 0)"
   
   With Range("C2")
      .FormulaArray = Formula1
      .Replace "XXXX", Formula2, xlPart
      .Replace "WWWW", Formula3, xlPart
   End With
End Sub
 
  • Like
Reactions: MHD

MHD

New Member
Joined
Mar 16, 2021
Messages
37
Office Version
  1. 2019
The formula is too long, try it like
VBA Code:
Sub Macro4()
   Dim Formula1 As String, Formula2 As String, Formula3 As String

   Formula1 = "=IF([@Unique]=1,TEXT([@[Date of Visits]],""dd.mm.yyyy""),""(""&TEXTJOIN("","",,IF(([i.Village]=[@[i.Village]])*([Month]=[@Month])*(XXXX=WWWW),TEXT([Date of Visits],""dd""),""""))&"");""&TEXT([@[Date of Visits]],""mm.yyyy""))"
   Formula2 = "MATCH([Date of Visits]&[i.Village],[Date of Visits]&[i.Village],0)"
   Formula3 = "Match(Row([Date of Visits]), Row([Date of Visits]), 0)"

   With Range("C2")
      .FormulaArray = Formula1
      .Replace "XXXX", Formula2, xlPart
      .Replace "WWWW", Formula3, xlPart
   End With
End Sub
Thank you @Fluff

There is still something wrong!

In the First table, I used the code to write the function
Whereas the second table I wrote manually and it works as you can see.

Analysis_Tool.xlsm
ABCDEFGHIJKLM
1Date of VisitsMonthDatei.Village#UniqueDate of VisitsMonthDatei.Village#Unique
205/04/2021405.04.2021Baruni1105/04/2021405.04.2021Baruni11
310/05/2021510.05.2021Baruni1110/05/2021510.05.2021Baruni11
406/04/2021406.04.2021Bettiah1106/04/2021406.04.2021Bettiah11
504/04/2021404.04.2021Bhagalpur1104/04/2021404.04.2021Bhagalpur11
605/04/20214#NAME?Bihar Sharif2205/04/20214(05,12);04.2021Bihar Sharif22
712/04/20214#NAME?Bihar Sharif2212/04/20214(05,12);04.2021Bihar Sharif22
812/04/2021412.04.2021Buxar1112/04/2021412.04.2021Buxar11
913/04/2021413.04.2021Chapra2113/04/2021413.04.2021Chapra21
1013/04/2021413.04.2021Chapra2113/04/2021413.04.2021Chapra21
Location Table
Cell Formulas
RangeFormula
I2:I10,B2:B10B2=MONTH([@[Date of Visits]])
C2C2=IF([@Unique]=1,TEXT([@[Date of Visits]],"dd.mm.yyyy"),"("&TEXTJOIN(",",,IF(([i.Village]=[@[i.Village]])*([Month]=[@Month])*(MATCH([Date of Visits]&[i.Village],[Date of Visits]&[i.Village],0)=MATCH(ROW([Date of Visits]), ROW([Date of Visits]), 0)),TEXT([Date of Visits],"dd"),""))&");"&TEXT([@[Date of Visits]],"mm.yyyy"))
C3:C10C3=IF([@Unique]=1,TEXT([@[Date of Visits]],"dd.mm.yyyy"),"("&TEXTJOIN(",",,IF(([i.Village]=[@[i.Village]])*([Month]=[@Month])*(XXXX=WWWW),TEXT([Date of Visits],"dd"),""))&");"&TEXT([@[Date of Visits]],"mm.yyyy"))
L2:L10,E2:E10E2=COUNTIFS([i.Village],[@[i.Village]],[Month],[@Month])
M2:M10,F2:F10F2=SUMPRODUCT(IF(([i.Village]=[@[i.Village]])*([Month]=[@Month]),1/(COUNTIFS([i.Village],[@[i.Village]],[Month],[@Month],[Date of Visits],[Date of Visits]))))
J2:J10J2=IF([@Unique]=1,TEXT([@[Date of Visits]],"dd.mm.yyyy"),"("&TEXTJOIN(",",,IF(([i.Village]=[@[i.Village]])*([Month]=[@Month])*(MATCH([Date of Visits]&[i.Village],[Date of Visits]&[i.Village],0)=MATCH(ROW([Date of Visits]),ROW([Date of Visits]),0)),TEXT([Date of Visits],"dd"),""))&");"&TEXT([@[Date of Visits]],"mm.yyyy"))
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,319
Office Version
  1. 365
Platform
  1. Windows
It looks to be down to the fact that it's a table & it fills the formula down automatically.
As I don't often use tables it's not something I've come across so not quite sure how to stop it.
 
  • Like
Reactions: MHD

MHD

New Member
Joined
Mar 16, 2021
Messages
37
Office Version
  1. 2019

ADVERTISEMENT

It looks to be down to the fact that it's a table & it fills the formula down automatically.
As I don't often use tables it's not something I've come across so not quite sure how to stop it.
I think the problem is in the replacement
Note that he does not replace symbols with functions in the final form
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,319
Office Version
  1. 365
Platform
  1. Windows
Brute force approach seems to work
VBA Code:
Sub Macro4()
   Dim Formula1 As String, Formula2 As String, Formula3 As String
   
   Formula1 = "=IF([@Unique]=1,TEXT([@[Date of Visits]],""dd.mm.yyyy""),""(""&TEXTJOIN("","",,IF(([i.Village]=[@[i.Village]])*([Month]=[@Month])*(XXXX=WWWW),TEXT([Date of Visits],""dd""),""""))&"");""&TEXT([@[Date of Visits]],""mm.yyyy""))"
   Formula2 = "MATCH([Date of Visits]&[i.Village],[Date of Visits]&[i.Village],0)"
   Formula3 = "Match(Row([Date of Visits]), Row([Date of Visits]), 0)"
   
   With Sheets("Location Table").ListObjects("Table2").ListColumns("Date")
      .Range(2).FormulaArray = Formula1
      .Range(2).FormulaArray = Formula1
      .Range(2).Replace "XXXX", Formula2, xlPart
      .Range(2).Replace "WWWW", Formula3, xlPart
     .DataBodyRange.FillDown
   End With
End Sub
Change sheet, table & column names to suit.
 
  • Like
Reactions: MHD
Solution

MHD

New Member
Joined
Mar 16, 2021
Messages
37
Office Version
  1. 2019

ADVERTISEMENT

Brute force approach seems to work
VBA Code:
Sub Macro4()
   Dim Formula1 As String, Formula2 As String, Formula3 As String
  
   Formula1 = "=IF([@Unique]=1,TEXT([@[Date of Visits]],""dd.mm.yyyy""),""(""&TEXTJOIN("","",,IF(([i.Village]=[@[i.Village]])*([Month]=[@Month])*(XXXX=WWWW),TEXT([Date of Visits],""dd""),""""))&"");""&TEXT([@[Date of Visits]],""mm.yyyy""))"
   Formula2 = "MATCH([Date of Visits]&[i.Village],[Date of Visits]&[i.Village],0)"
   Formula3 = "Match(Row([Date of Visits]), Row([Date of Visits]), 0)"
  
   With Sheets("Location Table").ListObjects("Table2").ListColumns("Date")
      .Range(2).FormulaArray = Formula1
      .Range(2).FormulaArray = Formula1
      .Range(2).Replace "XXXX", Formula2, xlPart
      .Range(2).Replace "WWWW", Formula3, xlPart
     .DataBodyRange.FillDown
   End With
End Sub
Change sheet, table & column names to suit.
Unfortunately, it still writes the correct function in the first cell only but not in all column
But what if we write the function in a range instead of into one cell C2
 

MHD

New Member
Joined
Mar 16, 2021
Messages
37
Office Version
  1. 2019
Unfortunately, it still writes the correct function in the first cell only but not in all column
But what if we write the function in a range instead of into one cell C2
It does not work too, error (unable to set formula array property of the range class 😞
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,319
Office Version
  1. 365
Platform
  1. Windows
But what if we write the function in a range instead of into one cell C2
You can't, not with an array formula. If it's putting it in the first cell correctly the fill down should then fill the correct formula to all cells.
 

MHD

New Member
Joined
Mar 16, 2021
Messages
37
Office Version
  1. 2019
@Fluff now It's working very well
I am so grateful for your support.
Brute force approach seems to work
VBA Code:
Sub Macro4()
   Dim Formula1 As String, Formula2 As String, Formula3 As String
  
   Formula1 = "=IF([@Unique]=1,TEXT([@[Date of Visits]],""dd.mm.yyyy""),""(""&TEXTJOIN("","",,IF(([i.Village]=[@[i.Village]])*([Month]=[@Month])*(XXXX=WWWW),TEXT([Date of Visits],""dd""),""""))&"");""&TEXT([@[Date of Visits]],""mm.yyyy""))"
   Formula2 = "MATCH([Date of Visits]&[i.Village],[Date of Visits]&[i.Village],0)"
   Formula3 = "Match(Row([Date of Visits]), Row([Date of Visits]), 0)"
  
   With Sheets("Location Table").ListObjects("Table2").ListColumns("Date")
      .Range(2).FormulaArray = Formula1
      .Range(2).FormulaArray = Formula1
      .Range(2).Replace "XXXX", Formula2, xlPart
      .Range(2).Replace "WWWW", Formula3, xlPart
     .DataBodyRange.FillDown
   End With
End Sub
Change sheet, table & column names to suit.
 

Forum statistics

Threads
1,141,203
Messages
5,704,941
Members
421,372
Latest member
Jamie11

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
Top