Hi all I am trying to do a few things here with VBA:
Here's the source Worksheet named MoCMatrix
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.
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):
Thank you in advance for your help.
Here's the source Worksheet named MoCMatrix
TEMPLATE_PRM Lav MoC Overview_Full Report.xlsm | |||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | |||
1 | HELPER ROW > | Structural Integrity-MoC | Structural Integrity-Substantiation Document | Structural Integrity-Statement | Flammability & Smoke-MoC | Flammability & Smoke-Substantiation Document | Flammability & Smoke-Statement | Handling & Abuse-MoC | Handling & Abuse-Substantiation Document | Handling & Abuse-Statement | Environmental Conditions-MoC | Environmental Conditions-Substantiation Document | Environmental Conditions-Statement | Endurance-MoC | Endurance-Substantiation Document | Endurance-Statement | Vibration-MoC | Vibration-Substantiation Document | Vibration-Statement | USPHS-MoC | USPHS-Substantiation Document | USPHS-Statement | |||
2 | TESTS > | Structural Integrity | Structural Integrity | Structural Integrity | Flammability & Smoke | Flammability & Smoke | Flammability & Smoke | Handling & Abuse | Handling & Abuse | Handling & Abuse | Environmental Conditions | Environmental Conditions | Environmental Conditions | Endurance | Endurance | Endurance | Vibration | Vibration | Vibration | USPHS | USPHS | USPHS | |||
3 | FUNCTION CODE | MoC, Substantiation Report, Statement > | MoC | Substantiation Document | Statement | MoC | Substantiation Document | Statement | MoC | Substantiation Document | Statement | MoC | Substantiation Document | Statement | MoC | Substantiation Document | Statement | MoC | Substantiation Document | Statement | MoC | Substantiation Document | Statement | ||
4 | 1.01 | Side Wall Panel | 4 | TBD | 4 | TBD | N/A | - | No handling & abuse load applicable to panels | 4 | TBD | N/A | - | No movable parts | 4 | TBD | N/A | - | |||||||
5 | 1.02 | Aisle Wall Panel | 4 | TBD | 4 | TBD | N/A | - | No handling & abuse load applicable to panels | 4 | TBD | N/A | - | No movable parts | 4 | TBD | N/A | - | |||||||
6 | 1.03 | Rear Wall Panel | 4 | TBD | 4 | TBD | N/A | - | No handling & abuse load applicable to panels | 4 | TBD | N/A | - | No movable parts | 4 | TBD | N/A | - | |||||||
7 | 1.04 | Ceiling Panel | 4 | TBD | 4 | TBD | N/A | - | No handling & abuse load applicable to panels | 4 | TBD | N/A | - | No movable parts | 4 | TBD | N/A | - | |||||||
8 | 1.05 | Floor Assy | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | N/A | - | No movable parts | 4 | TBD | N/A | - | ||||||||
9 | 1.06 | Misc Internal Panel | 4 | TBD | 4 | TBD | N/A | - | No handling & abuse load applicable to panels | 4 | TBD | N/A | - | No movable parts | 4 | TBD | N/A | - | |||||||
10 | 1.07 | PRM Expansion Panel | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | N/A | - | |||||||||
11 | 1.08 | Toilet Shroud Panel | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | N/A | - | No movable parts | 4 | TBD | N/A | - | ||||||||
12 | 1.09 | Single Blade Door | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | N/A | - | |||||||||
13 | 1.10 | Upper Attachment | 4 | TBD | 4 | TBD | N/A | - | No handling & abuse load applicable to panels | 4 | TBD | N/A | - | No movable parts | 4 | TBD | N/A | - | |||||||
14 | 1.11 | Lower Attachment | 4 | TBD | 4 | TBD | N/A | - | No handling & abuse load applicable to panels | 4 | TBD | N/A | - | No movable parts | 4 | TBD | N/A | - | |||||||
15 | 1.99 | Multiple Functions (Housing) | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | N/A | - | |||||||||
16 | 2.01 | Spot Light | N/A | - | Not applicable for light | 4 | TBD | 4 | TBD | 4 | TBD | N/A | - | No movable parts | 4 | TBD | N/A | - | |||||||
17 | 2.02 | Emergency Light | N/A | - | Not applicable for light | 4 | TBD | 4 | TBD | 4 | TBD | N/A | - | No movable parts | 4 | TBD | N/A | - | |||||||
18 | 2.03 | Exit Sign | N/A | - | Not applicable for light | 4 | TBD | N/A | - | No handling & abuse load applicable to panels | 4 | TBD | N/A | - | No movable parts | 4 | TBD | N/A | - | ||||||
19 | 2.04 | Speaker | N/A | - | Not applicable for light | 4 | TBD | N/A | - | No handling & abuse load applicable to panels | 4 | TBD | N/A | - | No movable parts | 4 | TBD | N/A | - | ||||||
20 | 2.05 | Crew Call Light | N/A | - | Not applicable for light | 4 | TBD | 4 | TBD | 4 | TBD | N/A | - | No movable parts | 4 | TBD | N/A | - | |||||||
21 | 2.06 | Door Switch | N/A | - | Not applicable for light | 4 | TBD | 4 | TBD | 4 | TBD | N/A | - | No movable parts | 4 | TBD | N/A | - | |||||||
22 | 2.07 | Grounding | 4 | TBD | 4 | TBD | N/A | - | No handling & abuse load applicable to panels | 4 | TBD | N/A | - | No movable parts | 4 | TBD | N/A | - | |||||||
23 | 2.99 | Multiple Functions (Electrics) | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | N/A | - | |||||||||
24 | 3.01 | Air Extraction | 4 | TBD | 4 | TBD | N/A | - | Not exposed to loads applied by passenger/crew | 0 | TBD | N/A | - | No movable parts | 4 | TBD | N/A | - | |||||||
25 | 3.02 | Water Heater | 4 | TBD | 4 | TBD | 4 | TBD | 0 | TBD | N/A | - | No movable parts | 4 | TBD | N/A | - | ||||||||
26 | 3.03 | Piping Potable Water | 4 | TBD | 4 | TBD | N/A | - | Not exposed to loads applied by passenger/crew | 0 | TBD | N/A | - | No movable parts | 4 | TBD | 2 | TBD | |||||||
27 | 3.04 | Shutt-off Valve | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | N/A | - | No movable parts | 4 | TBD | 2 | TBD | ||||||||
28 | 3.05 | Water Faucet | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | |||||||||
29 | 3.06 | Drain Assy | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | N/A | - | No movable parts | 4 | TBD | 4 | TBD | ||||||||
30 | 3.07 | Piping Waste Water | 4 | TBD | 4 | TBD | N/A | - | Not exposed to loads applied by passenger/crew | 4 | TBD | N/A | - | No movable parts | 4 | TBD | 4 | TBD | |||||||
31 | 3.08 | Drain Valve | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | N/A | - | No movable parts | 4 | TBD | 4 | TBD | ||||||||
32 | 3.09 | Piping Vacuum | 4 | TBD | 4 | TBD | N/A | - | Not exposed to loads applied by passenger/crew | 4 | TBD | N/A | - | No movable parts | 4 | TBD | 4 | TBD | |||||||
33 | 3.10 | Water Filter | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | N/A | - | No movable parts | 4 | TBD | 4 | TBD | ||||||||
34 | 3.11 | Gasper | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | N/A | - | No movable parts | 4 | TBD | 4 | TBD | ||||||||
35 | 3.99 | Multiple Functions (Systems) | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | |||||||||
36 | 4.01 | Vanity Assy | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | |||||||||
37 | 4.02 | Service Cabinet Assy (Countertop) | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | |||||||||
38 | 4.03 | Service Cabinet Assy (Upper) | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | |||||||||
39 | 4.04 | Service Cabinet Assy (Lower) | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | |||||||||
40 | 4.05 | Nursing Table | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | |||||||||
41 | 4.06 | Toilet Assy | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | |||||||||
42 | 4.07 | Toilet Lid | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | |||||||||
43 | 4.08 | Toilet Seat | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | |||||||||
44 | 4.09 | Soap Dispenser | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | |||||||||
45 | 4.10 | Hand Tissue Dispenser | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | |||||||||
46 | 4.11 | Toilet Roll Holder | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | |||||||||
47 | 4.12 | PRM Expansion Slides | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | |||||||||
48 | 4.13 | Waste Compartment | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | |||||||||
49 | 4.14 | Handle Assy (Hinged) | 2 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | |||||||||
50 | 4.15 | Handle Assy (Fixed) | 2 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | N/A | - | No movable parts | 4 | TBD | 4 | TBD | ||||||||
51 | 4.16 | Slides | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | |||||||||
52 | 4.17 | Latches | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | |||||||||
53 | 4.18 | Hinges | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | |||||||||
54 | 4.19 | Mirror | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | N/A | - | No movable parts | 4 | TBD | 4 | TBD | ||||||||
55 | 4.20 | Oxygen Container | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | |||||||||
56 | 4.21 | Smoke Detector | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | N/A | - | No movable parts | 4 | TBD | 4 | TBD | ||||||||
57 | 4.22 | Fire Extinguisher | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | N/A | - | No movable parts | 4 | TBD | 4 | TBD | ||||||||
58 | 4.23 | Flush Switch | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | |||||||||
59 | 4.99 | Multiple Functions (Components) | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | |||||||||
60 | 5.01 | Other - Assembly/Product | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | |||||||||
61 | 5.02 | Other - Standard Parts | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | |||||||||
62 | 5.03 | Other - TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | |||||||||
63 | 5.99 | Multiple Functions (Mixed) | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | 4 | TBD | |||||||||
MoCMatrix |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C1:W1 | C1 | =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 | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
1 | Function Code | Function Sub-Category | Assy P/N | Component P/N | Tests | MoC | Substantiation Document | Statement | ||
2 | 1.01 | Structural Integrity | 4 | TBD | 0 | |||||
3 | Flammability & Smoke | 4 | TBD | 0 | ||||||
4 | Handling & Abuse | N/A | - | No handling & abuse load applicable to panels | ||||||
5 | Environmental Conditions | 4 | TBD | 0 | ||||||
6 | Endurance | N/A | - | No movable parts | ||||||
7 | Vibration | 4 | TBD | 0 | ||||||
8 | USPHS | N/A | - | 0 | ||||||
9 | 1.02 | Structural Integrity | ||||||||
10 | Flammability & Smoke | |||||||||
11 | Handling & Abuse | |||||||||
12 | Environmental Conditions | |||||||||
13 | Endurance | |||||||||
14 | Vibration | |||||||||
15 | USPHS | |||||||||
16 | 1.03 | Structural Integrity | ||||||||
17 | Flammability & Smoke | |||||||||
18 | Handling & Abuse | |||||||||
19 | Environmental Conditions | |||||||||
20 | Endurance | |||||||||
21 | Vibration | |||||||||
22 | USPHS | |||||||||
23 | 1.04 | Structural Integrity | ||||||||
24 | Flammability & Smoke | |||||||||
25 | Handling & Abuse | |||||||||
26 | Environmental Conditions | |||||||||
27 | Endurance | |||||||||
28 | Vibration | |||||||||
29 | USPHS | |||||||||
30 | 1.05 | Structural Integrity | ||||||||
31 | Flammability & Smoke | |||||||||
32 | Handling & Abuse | |||||||||
33 | Environmental Conditions | |||||||||
34 | Endurance | |||||||||
35 | Vibration | |||||||||
36 | USPHS | |||||||||
FB1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F2:H8 | F2 | =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.