How to insert an Array formula in VBA

M

MHD

New Member
Joined
Mar 16, 2021
Messages
49
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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sort by date Sort by votes
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
Upvote 0
Fluff said:
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
Click to expand...
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:
Upvote 0
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
Upvote 0
Fluff said:
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.
Click to expand...
I think the problem is in the replacement
Note that he does not replace symbols with functions in the final form
 
Upvote 0
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
Upvote 0
Solution
Fluff said:
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.
Click to expand...
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
 
Upvote 0
MHD said:
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
Click to expand...
It does not work too, error (unable to set formula array property of the range class ?
 
Upvote 0
MHD said:
But what if we write the function in a range instead of into one cell C2
Click to expand...
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.
 
Upvote 0
@Fluff now It's working very well
I am so grateful for your support.
Fluff said:
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.
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Solved
Problems filtering a Pivot Table with VBA
Replies
2
Views
175
Domenic
D
R
  • Question
VBA loop through date type mismatch
2
Replies
19
Views
766
Georgiboy
Georgiboy
L
  • Solved
Would like to make edits to VBA Calendar Code
Replies
8
Views
384
Legacy 352679
L
Agnarr
  • Question
VBA problem with creating new sheets
Replies
0
Views
338
Agnarr
Agnarr
J
  • Solved
VBA to calculate and conditional format cell
Replies
6
Views
183
rollis13
rollis13

Forum statistics

Threads
1,214,652
Messages
6,120,747
Members
448,989
Latest member
mariah3

Share this page

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