HighAndWilder
Well-known Member
- Joined
- Nov 4, 2006
- Messages
- 702
- Office Version
- 365
- Platform
- Windows
I'm just enhancing my skills and I have hit a problem.
The formula that I write to the workbook evaluates fine but when I try to assign the values to an array I get an error 13 Type Mismatch.
arr = Application.Evaluate(strFormula)
This is Work In Progress but if doing this is not possible than I will adopt a different approach.
What am I missing?
Thanks
The formula that I write to the workbook evaluates fine but when I try to assign the values to an array I get an error 13 Type Mismatch.
arr = Application.Evaluate(strFormula)
This is Work In Progress but if doing this is not possible than I will adopt a different approach.
What am I missing?
Thanks
VBA Code:
Public Sub subTestCode()
Dim strFormula As String
Dim Q As String
Dim strSplit As String
Dim strFormat As String
Dim WsSource As Worksheet
Dim WsDestination As Worksheet
Dim arr() As Variant
ActiveWorkbook.Save
Q = Chr(34)
Set WsSource = Worksheets("TestData")
Set WsDestination = Worksheets("Sheet9")
WsDestination.Cells.Clear
strSplit = "Column 4"
strFormat = "DD/MM/YYYY"
strFormula = "LET(allcells,TestData!1:1048576,rows,COUNTA(TestData!$A:$A),cols,COUNTA(TestData!$1:$1),vsplit," & _
"MATCH(" & Q & strSplit & Q & ",TestData!$1:$1),valuecols,cols-vsplit,HSTACK(" & _
"CHOOSEROWS(TRANSPOSE(TEXT(INDEX(TestData!$1:$1,1,vsplit+1):INDEX(TestData!$1:$1,1,cols)," & Q & "DD/MM/YYYY" & Q & "))," & _
"MOD(SEQUENCE(valuecols*(rows-1),,0),valuecols)+1),CHOOSEROWS(INDEX(allcells,2,1):INDEX(allcells,rows,vsplit),MOD(SEQUENCE(valuecols*(" & _
"rows-1),,0),(rows-1))+1),TOCOL(INDEX(allcells,2,5):INDEX(allcells,rows,cols))))"
arr = Application.Evaluate(strFormula)
WsDestination.Range("A1").Formula2 = "=" & strFormula
End Sub
Transpose Data.xlsm | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
1 | Column 1 | Column 2 | Column 3 | Column 4 | 01/01/2024 | 02/01/2024 | 03/01/2024 | 04/01/2024 | ||
2 | Dummy 2 1 | Dummy 2 2 | Dummy 2 3 | Dummy 2 4 | 1 | 8 | 3 | 6 | ||
3 | Dummy 3 1 | Dummy 3 2 | Dummy 3 3 | Dummy 3 4 | 9 | 10 | 9 | 4 | ||
4 | Dummy 4 1 | Dummy 4 2 | Dummy 4 3 | Dummy 4 4 | 8 | 5 | 9 | 8 | ||
5 | Dummy 5 1 | Dummy 5 2 | Dummy 5 3 | Dummy 5 4 | 1 | 7 | 9 | 9 | ||
6 | Dummy 6 1 | Dummy 6 2 | Dummy 6 3 | Dummy 6 4 | 1 | 1 | 6 | 9 | ||
7 | Dummy 7 1 | Dummy 7 2 | Dummy 7 3 | Dummy 7 4 | 1 | 5 | 9 | 6 | ||
8 | Dummy 8 1 | Dummy 8 2 | Dummy 8 3 | Dummy 8 4 | 5 | 2 | 6 | 6 | ||
TestData |
Transpose Data.xlsm | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | 01/01/2024 | Dummy 2 1 | Dummy 2 2 | Dummy 2 3 | Dummy 2 4 | 1 | ||
2 | 02/01/2024 | Dummy 3 1 | Dummy 3 2 | Dummy 3 3 | Dummy 3 4 | 8 | ||
3 | 03/01/2024 | Dummy 4 1 | Dummy 4 2 | Dummy 4 3 | Dummy 4 4 | 3 | ||
4 | 04/01/2024 | Dummy 5 1 | Dummy 5 2 | Dummy 5 3 | Dummy 5 4 | 6 | ||
5 | 01/01/2024 | Dummy 6 1 | Dummy 6 2 | Dummy 6 3 | Dummy 6 4 | 9 | ||
6 | 02/01/2024 | Dummy 7 1 | Dummy 7 2 | Dummy 7 3 | Dummy 7 4 | 10 | ||
7 | 03/01/2024 | Dummy 8 1 | Dummy 8 2 | Dummy 8 3 | Dummy 8 4 | 9 | ||
8 | 04/01/2024 | Dummy 2 1 | Dummy 2 2 | Dummy 2 3 | Dummy 2 4 | 4 | ||
9 | 01/01/2024 | Dummy 3 1 | Dummy 3 2 | Dummy 3 3 | Dummy 3 4 | 8 | ||
10 | 02/01/2024 | Dummy 4 1 | Dummy 4 2 | Dummy 4 3 | Dummy 4 4 | 5 | ||
11 | 03/01/2024 | Dummy 5 1 | Dummy 5 2 | Dummy 5 3 | Dummy 5 4 | 9 | ||
12 | 04/01/2024 | Dummy 6 1 | Dummy 6 2 | Dummy 6 3 | Dummy 6 4 | 8 | ||
13 | 01/01/2024 | Dummy 7 1 | Dummy 7 2 | Dummy 7 3 | Dummy 7 4 | 1 | ||
14 | 02/01/2024 | Dummy 8 1 | Dummy 8 2 | Dummy 8 3 | Dummy 8 4 | 7 | ||
15 | 03/01/2024 | Dummy 2 1 | Dummy 2 2 | Dummy 2 3 | Dummy 2 4 | 9 | ||
16 | 04/01/2024 | Dummy 3 1 | Dummy 3 2 | Dummy 3 3 | Dummy 3 4 | 9 | ||
17 | 01/01/2024 | Dummy 4 1 | Dummy 4 2 | Dummy 4 3 | Dummy 4 4 | 1 | ||
18 | 02/01/2024 | Dummy 5 1 | Dummy 5 2 | Dummy 5 3 | Dummy 5 4 | 1 | ||
19 | 03/01/2024 | Dummy 6 1 | Dummy 6 2 | Dummy 6 3 | Dummy 6 4 | 6 | ||
20 | 04/01/2024 | Dummy 7 1 | Dummy 7 2 | Dummy 7 3 | Dummy 7 4 | 9 | ||
21 | 01/01/2024 | Dummy 8 1 | Dummy 8 2 | Dummy 8 3 | Dummy 8 4 | 1 | ||
22 | 02/01/2024 | Dummy 2 1 | Dummy 2 2 | Dummy 2 3 | Dummy 2 4 | 5 | ||
23 | 03/01/2024 | Dummy 3 1 | Dummy 3 2 | Dummy 3 3 | Dummy 3 4 | 9 | ||
24 | 04/01/2024 | Dummy 4 1 | Dummy 4 2 | Dummy 4 3 | Dummy 4 4 | 6 | ||
25 | 01/01/2024 | Dummy 5 1 | Dummy 5 2 | Dummy 5 3 | Dummy 5 4 | 5 | ||
26 | 02/01/2024 | Dummy 6 1 | Dummy 6 2 | Dummy 6 3 | Dummy 6 4 | 2 | ||
27 | 03/01/2024 | Dummy 7 1 | Dummy 7 2 | Dummy 7 3 | Dummy 7 4 | 6 | ||
28 | 04/01/2024 | Dummy 8 1 | Dummy 8 2 | Dummy 8 3 | Dummy 8 4 | 6 | ||
Sheet9 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
A1:F28 | A1 | =LET(allcells,TestData!1:1048576,rows,COUNTA(TestData!$A:$A),cols,COUNTA(TestData!$1:$1),vsplit,MATCH("Column 4",TestData!$1:$1),valuecols,cols-vsplit,HSTACK(CHOOSEROWS(TRANSPOSE(TEXT(INDEX(TestData!$1:$1,1,vsplit+1):INDEX(TestData!$1:$1,1,cols),"DD/MM/YYYY")),MOD(SEQUENCE(valuecols*(rows-1),,0),valuecols)+1),CHOOSEROWS(INDEX(allcells,2,1):INDEX(allcells,rows,vsplit),MOD(SEQUENCE(valuecols*(rows-1),,0),(rows-1))+1),TOCOL(INDEX(allcells,2,5):INDEX(allcells,rows,cols)))) |
Dynamic array formulas. |