VBA FormulaArray Syntax/AutoFill/Set Ranges

U

UlyssesFR

New Member
Joined
Jun 30, 2020
Messages
14
Office Version
  1. 365
Platform
  1. Windows
Hi all I am trying to do a few things here with VBA:

Here's the source Worksheet named MoCMatrix

TEMPLATE_PRM Lav MoC Overview_Full Report.xlsm
ABCDEFGHIJKLMNOPQRSTUVW
1HELPER ROW > Structural Integrity-MoCStructural Integrity-Substantiation DocumentStructural Integrity-StatementFlammability & Smoke-MoCFlammability & Smoke-Substantiation DocumentFlammability & Smoke-StatementHandling & Abuse-MoCHandling & Abuse-Substantiation DocumentHandling & Abuse-StatementEnvironmental Conditions-MoCEnvironmental Conditions-Substantiation DocumentEnvironmental Conditions-StatementEndurance-MoCEndurance-Substantiation DocumentEndurance-StatementVibration-MoCVibration-Substantiation DocumentVibration-StatementUSPHS-MoCUSPHS-Substantiation DocumentUSPHS-Statement
2TESTS > Structural IntegrityStructural IntegrityStructural IntegrityFlammability & SmokeFlammability & SmokeFlammability & SmokeHandling & AbuseHandling & AbuseHandling & AbuseEnvironmental ConditionsEnvironmental ConditionsEnvironmental ConditionsEnduranceEnduranceEnduranceVibrationVibrationVibrationUSPHSUSPHSUSPHS
3FUNCTION CODEMoC, Substantiation Report, Statement > MoCSubstantiation DocumentStatementMoCSubstantiation DocumentStatementMoCSubstantiation DocumentStatementMoCSubstantiation DocumentStatementMoCSubstantiation DocumentStatementMoCSubstantiation DocumentStatementMoCSubstantiation DocumentStatement
41.01Side Wall Panel4TBD4TBDN/A-No handling & abuse load applicable to panels4TBDN/A-No movable parts4TBDN/A-
51.02Aisle Wall Panel4TBD4TBDN/A-No handling & abuse load applicable to panels4TBDN/A-No movable parts4TBDN/A-
61.03Rear Wall Panel4TBD4TBDN/A-No handling & abuse load applicable to panels4TBDN/A-No movable parts4TBDN/A-
71.04Ceiling Panel4TBD4TBDN/A-No handling & abuse load applicable to panels4TBDN/A-No movable parts4TBDN/A-
81.05Floor Assy4TBD4TBD4TBD4TBDN/A-No movable parts4TBDN/A-
91.06Misc Internal Panel4TBD4TBDN/A-No handling & abuse load applicable to panels4TBDN/A-No movable parts4TBDN/A-
101.07PRM Expansion Panel4TBD4TBD4TBD4TBD4TBD4TBDN/A-
111.08Toilet Shroud Panel4TBD4TBD4TBD4TBDN/A-No movable parts4TBDN/A-
121.09Single Blade Door4TBD4TBD4TBD4TBD4TBD4TBDN/A-
131.10Upper Attachment4TBD4TBDN/A-No handling & abuse load applicable to panels4TBDN/A-No movable parts4TBDN/A-
141.11Lower Attachment4TBD4TBDN/A-No handling & abuse load applicable to panels4TBDN/A-No movable parts4TBDN/A-
151.99Multiple Functions (Housing)4TBD4TBD4TBD4TBD4TBD4TBDN/A-
162.01Spot LightN/A-Not applicable for light4TBD4TBD4TBDN/A-No movable parts4TBDN/A-
172.02Emergency LightN/A-Not applicable for light4TBD4TBD4TBDN/A-No movable parts4TBDN/A-
182.03Exit SignN/A-Not applicable for light4TBDN/A-No handling & abuse load applicable to panels4TBDN/A-No movable parts4TBDN/A-
192.04SpeakerN/A-Not applicable for light4TBDN/A-No handling & abuse load applicable to panels4TBDN/A-No movable parts4TBDN/A-
202.05Crew Call LightN/A-Not applicable for light4TBD4TBD4TBDN/A-No movable parts4TBDN/A-
212.06Door SwitchN/A-Not applicable for light4TBD4TBD4TBDN/A-No movable parts4TBDN/A-
222.07Grounding4TBD4TBDN/A-No handling & abuse load applicable to panels4TBDN/A-No movable parts4TBDN/A-
232.99Multiple Functions (Electrics)4TBD4TBD4TBD4TBD4TBD4TBDN/A-
243.01Air Extraction4TBD4TBDN/A-Not exposed to loads applied by passenger/crew0TBDN/A-No movable parts4TBDN/A-
253.02Water Heater4TBD4TBD4TBD0TBDN/A-No movable parts4TBDN/A-
263.03Piping Potable Water4TBD4TBDN/A-Not exposed to loads applied by passenger/crew0TBDN/A-No movable parts4TBD2TBD
273.04Shutt-off Valve4TBD4TBD4TBD4TBDN/A-No movable parts4TBD2TBD
283.05Water Faucet4TBD4TBD4TBD4TBD4TBD4TBD4TBD
293.06Drain Assy4TBD4TBD4TBD4TBDN/A-No movable parts4TBD4TBD
303.07Piping Waste Water4TBD4TBDN/A-Not exposed to loads applied by passenger/crew4TBDN/A-No movable parts4TBD4TBD
313.08Drain Valve4TBD4TBD4TBD4TBDN/A-No movable parts4TBD4TBD
323.09Piping Vacuum4TBD4TBDN/A-Not exposed to loads applied by passenger/crew4TBDN/A-No movable parts4TBD4TBD
333.10Water Filter4TBD4TBD4TBD4TBDN/A-No movable parts4TBD4TBD
343.11Gasper4TBD4TBD4TBD4TBDN/A-No movable parts4TBD4TBD
353.99Multiple Functions (Systems)4TBD4TBD4TBD4TBD4TBD4TBD4TBD
364.01Vanity Assy4TBD4TBD4TBD4TBD4TBD4TBD4TBD
374.02Service Cabinet Assy (Countertop)4TBD4TBD4TBD4TBD4TBD4TBD4TBD
384.03Service Cabinet Assy (Upper)4TBD4TBD4TBD4TBD4TBD4TBD4TBD
394.04Service Cabinet Assy (Lower)4TBD4TBD4TBD4TBD4TBD4TBD4TBD
404.05Nursing Table4TBD4TBD4TBD4TBD4TBD4TBD4TBD
414.06Toilet Assy4TBD4TBD4TBD4TBD4TBD4TBD4TBD
424.07Toilet Lid4TBD4TBD4TBD4TBD4TBD4TBD4TBD
434.08Toilet Seat4TBD4TBD4TBD4TBD4TBD4TBD4TBD
444.09Soap Dispenser4TBD4TBD4TBD4TBD4TBD4TBD4TBD
454.10Hand Tissue Dispenser4TBD4TBD4TBD4TBD4TBD4TBD4TBD
464.11Toilet Roll Holder4TBD4TBD4TBD4TBD4TBD4TBD4TBD
474.12PRM Expansion Slides4TBD4TBD4TBD4TBD4TBD4TBD4TBD
484.13Waste Compartment4TBD4TBD4TBD4TBD4TBD4TBD4TBD
494.14Handle Assy (Hinged)2TBD4TBD4TBD4TBD4TBD4TBD4TBD
504.15Handle Assy (Fixed)2TBD4TBD4TBD4TBDN/A-No movable parts4TBD4TBD
514.16Slides4TBD4TBD4TBD4TBD4TBD4TBD4TBD
524.17Latches4TBD4TBD4TBD4TBD4TBD4TBD4TBD
534.18Hinges4TBD4TBD4TBD4TBD4TBD4TBD4TBD
544.19Mirror4TBD4TBD4TBD4TBDN/A-No movable parts4TBD4TBD
554.20Oxygen Container4TBD4TBD4TBD4TBD4TBD4TBD4TBD
564.21Smoke Detector4TBD4TBD4TBD4TBDN/A-No movable parts4TBD4TBD
574.22Fire Extinguisher4TBD4TBD4TBD4TBDN/A-No movable parts4TBD4TBD
584.23Flush Switch4TBD4TBD4TBD4TBD4TBD4TBD4TBD
594.99Multiple Functions (Components)4TBD4TBD4TBD4TBD4TBD4TBD4TBD
605.01Other - Assembly/Product4TBD4TBD4TBD4TBD4TBD4TBD4TBD
615.02Other - Standard Parts4TBD4TBD4TBD4TBD4TBD4TBD4TBD
625.03Other - TBD4TBD4TBD4TBD4TBD4TBD4TBD4TBD
635.99Multiple Functions (Mixed)4TBD4TBD4TBD4TBD4TBD4TBD4TBD
MoCMatrix
Cell Formulas
RangeFormula
C1:W1C1=C2&"-"&C3


I'm trying to distribute the range C4:W63 into a series of other worksheets based on three criteria:
Criteria 1 is the Function Codes in range A4:A63
Criteria 2 is the Tests in range C2:W2
Criteria 3 is the value in the range C3:W3

I have combined Criteria 2 and 3 into a helper row in C1:W1

The recipient worksheets are all in this format. In this instance, I have manually filled the range F2:H8 with Array Formulas to show you what I'm after.

TEMPLATE_PRM Lav MoC Overview_Full Report.xlsm
ABCDEFGH
1Function CodeFunction Sub-CategoryAssy P/NComponent P/NTestsMoCSubstantiation DocumentStatement
21.01Structural Integrity4TBD0
3Flammability & Smoke4TBD0
4Handling & AbuseN/A-No handling & abuse load applicable to panels
5Environmental Conditions4TBD0
6EnduranceN/A-No movable parts
7Vibration4TBD0
8USPHSN/A-0
91.02Structural Integrity
10Flammability & Smoke
11Handling & Abuse
12Environmental Conditions
13Endurance
14Vibration
15USPHS
161.03Structural Integrity
17Flammability & Smoke
18Handling & Abuse
19Environmental Conditions
20Endurance
21Vibration
22USPHS
231.04Structural Integrity
24Flammability & Smoke
25Handling & Abuse
26Environmental Conditions
27Endurance
28Vibration
29USPHS
301.05Structural Integrity
31Flammability & Smoke
32Handling & Abuse
33Environmental Conditions
34Endurance
35Vibration
36USPHS
FB1
Cell Formulas
RangeFormula
F2:H8F2=INDEX(MoCMatrix!$C$4:$W$63,MATCH($A$2&"",MoCMatrix!$A$4:$A$63,0),MATCH($E2&"-"&F$1,MoCMatrix!$C$1:$W$1,0))
Press CTRL+SHIFT+ENTER to enter array formulas.


I'm trying to automate the distribution of these array formulas using VBA. The Array Formula works when entered manually, I'm trying to translate that to VBA, but I'm struggling with the VBA syntax and getting an "Unable to set the FormulaArray property of the Range class". Here's my code so far (Forgive me for the syntax, I'm sure there are a few things wrong with the following):

VBA Code: 
Sub No6_Import_and_Fill_MoC()
'
Application.ScreenUpdating = False
'
Dim TWB As Workbook
'
Dim WS As Worksheet
'
Dim T, U As Integer
Dim TRwCnt, URwCnt As Integer
Dim T_Test As Integer
'
Dim RNG_MatchFunc As Range
Dim RNG_MatchTest As Range
Dim RNG_Return As Range
'
Dim STR_MoC As String
'
T_Test = 7 '{"Structural Integrity", "Flammability & Smoke", "Handling & Abuse", "Environmental Conditions", "Endurance", "Vibration", "USPHS"}
'
Set TWB = ThisWorkbook
'
STR_MoC = "MoCMatrix"
'
Set RNG_MatchFunc = TWB.Worksheets(STR_MoC).Range("A4:A63")
Set RNG_MatchTest = TWB.Worksheets(STR_MoC).Range("C1:W1")
Set RNG_Return = TWB.Worksheets(STR_MoC).Range("C4:W63")
'
For Each WS In TWB.Worksheets
        With TWB.Worksheets(WS.Name)
            If WS.Name Like "FB*" Then
                .Activate
                TRwCnt = .Cells(.Rows.Count, "E").End(xlUp).Row
                    For T = TRwCnt To 2 Step -1
                        If .Range("A" & T & "") Like "?.?*" Then
                            .Range("F" & T & "").FormulaArray = "=INDEX(" & RNG_Return.Address(External:=True) & ",MATCH($A & T," & RNG_MatchFunc.Address(External:=True) & ",0),MATCH($E & T & ""-"" & $F$1," & RNG_MatchTest.Address(External:=True) & ",0))"
                            .Range("F" & T & "").AutoFill Range("F" & T & ":F" & T + T_Test - 1 & "")
                            .Range("G" & T & "").FormulaArray = "=INDEX(" & RNG_Return.Address(External:=True) & ",MATCH($A & T," & RNG_MatchFunc.Address(External:=True) & ",0),MATCH($E & T & ""-"" & $G$1," & RNG_MatchTest.Address(External:=True) & ",0))"
                            .Range("G" & T & "").AutoFill Range("G" & T & ":G" & T + T_Test - 1 & "")
                            .Range("H" & T & "").FormulaArray = "=INDEX(" & RNG_Return.Address(External:=True) & ",MATCH($A & T," & RNG_MatchFunc.Address(External:=True) & ",0),MATCH($E & T & ""-"" & $H$1," & RNG_MatchTest.Address(External:=True) & ",0))"
                            .Range("H" & T & "").AutoFill Range("H" & T & ":H" & T + T_Test - 1 & "")
                        End If
                    Next T
            End If
        End With
Next WS
'
Application.ScreenUpdating = True
'
End Sub

Thank you in advance for your help.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
I'm also open to any other solution that do not involve the use of array formulas to distribute that data :)
 
Upvote 0
Well, I have sussed it out in the end.

I'm sure it's not the most elegant/efficient way of doing it, but it does the job perfectly:

VBA Code: 
Sub No6_Import_and_Fill_MoC()
'
Application.ScreenUpdating = False
'
Dim TWB As Workbook
'
Dim WS As Worksheet
'
Dim T As Integer
Dim TRwCnt As Integer
Dim T_Test As Integer
'
Dim STR_MoC As String
'
T_Test = 7 '{"Structural Integrity", "Flammability & Smoke", "Handling & Abuse", "Environmental Conditions", "Endurance", "Vibration", "USPHS"}
'
Set TWB = ThisWorkbook
'
STR_MoC = "MoCMatrix"
'
For Each WS In TWB.Worksheets
        With TWB.Worksheets(WS.Name)
            If WS.Name Like "FB*" Then
                .Activate
                TRwCnt = .Cells(.Rows.Count, "E").End(xlUp).Row
                    For T = TRwCnt To 2 Step -1
                        If .Range("A" & T & "") Like "?.?*" Then
                            .Range("F" & T & "").Formula = "=INDEX('" & Worksheets(STR_MoC).Name & "'!$C$4:$W$63,MATCH($A$" & T & "&"""",'" & Worksheets(STR_MoC).Name & "'!$A$4:$A$63,0),MATCH($E2 & ""-"" & F$1,'" & Worksheets(STR_MoC).Name & "'!$C$1:$W$1,0))"
                            .Range("F" & T & "").AutoFill Range("F" & T & ":F" & T + T_Test - 1 & "")
                            .Range("G" & T & "").Formula = "=INDEX('" & Worksheets(STR_MoC).Name & "'!$C$4:$W$63,MATCH($A$" & T & "&"""",'" & Worksheets(STR_MoC).Name & "'!$A$4:$A$63,0),MATCH($E2 & ""-"" & G$1,'" & Worksheets(STR_MoC).Name & "'!$C$1:$W$1,0))"
                            .Range("G" & T & "").AutoFill Range("G" & T & ":G" & T + T_Test - 1 & "")
                            .Range("H" & T & "").Formula = "=INDEX('" & Worksheets(STR_MoC).Name & "'!$C$4:$W$63,MATCH($A$" & T & "&"""",'" & Worksheets(STR_MoC).Name & "'!$A$4:$A$63,0),MATCH($E2 & ""-"" & H$1,'" & Worksheets(STR_MoC).Name & "'!$C$1:$W$1,0))"
                            .Range("H" & T & "").AutoFill Range("H" & T & ":H" & T + T_Test - 1 & "")
                        End If
                    Next T
                .Range("F2:H" & TRwCnt).Value2 = .Range("F2:H" & TRwCnt).Value2
            End If
        End With
Next WS
'
Application.ScreenUpdating = True
'
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Work around script for when VBA array formula gives error 1004 Unable to set the FormulaArray property of the Range class
Replies
0
Views
729
johnnyL
J
R
  • Question Question
VBA Automation Error - possibly born from my complete lack of understanding with Error Handling
2 3
Replies
23
Views
9K
RockandGrohl
R
F
  • Question Question
How to speed up index match function
2 3
Replies
29
Views
5K
fluffyvampirekitten
F

Forum statistics

Threads
1,223,886
Messages
6,175,191
Members
452,616
Latest member
intern444

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