我试图将单元格公式移动到VBA，因为否则它总是重新计算，即使我停用excel选项，当我重新打开文件时它也会回来。这就是为什么我要将公式移动到VBA，只有当我按下按钮时才会发生，这更智能。
我有一个包含数据的主表，我将其聚合和索引，并在表中的另一个工作表中表示它。→列A到S都在主表中，在聚合表中，我只会有列A、C、E、G、H、I、J、K、L、M和P
我想移动到VBA的公式如下:
=IFERROR(INDEX(Endkontrolle!$A:$S;AGGREGATE(15;6;ROW(Endkontrolle!$A:$S)/((FIND($B$3;Endkontrolle!$F:$F;1)>0)*(Endkontrolle!$S:$S="x"));ROW()-32)-0;1);"")
有人能帮我把公式翻译成VBA脚本吗?
thank you very much
试试下面的代码:
Sub Button1_Click2()
'Declarations.
Dim RngTable As Range
Dim RngTarget As Range
Dim StrColumnsIndex As String
'A string is used to stores the index of the columns to be copied.
StrColumnsIndex = "1;3;5;7;8;9;10;11;14;15;16"
'RngTable is set as the range that will host the aggregated table.
Set RngTable = Sheets("Aggregated sheet").Range("A33:K34") '< EDIT THIS LINE ACCORDGLY TO YOU NEED
'Clearing RngTable.
RngTable.ClearContents
'Checking if StrColumnsIndex and RngTable are compatible.
If UBound(Split(StrColumnsIndex, ";")) + 1 <> RngTable.Columns.Count Then
MsgBox "The number of columns requested via StrColumnsIndex and the number of columns avaiable in RngTable do not match. Redefine the variables properly. The aggregated table will not be updated.", vbCritical + vbOKOnly, "Variable mismatch"
Exit Sub
End If
'Covering each cell in RngTable.
For Each RngTarget In RngTable
'The result is reported in each cell. The [row] element of the INDEX is obtained by subtracting _
RngTable.Row from the RngTarget.Row and adding one. This way each row is properly reported. The _
[col] element of the INDEX is obrained by splitting StrColumnsIndex using the difference between _
the RngTarget.Column and RngTable.Column as index. This way each requested column as listed in _
StrColumnsIndex is reported.
'RngTarget.Formula = "=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x""))," & RngTarget.Row - RngTable.Row + 1 & ")-0," & Split(StrColumnsIndex, ";")(RngTarget.Column - RngTable.Column) * 1 & "),"""")"
RngTarget.Value = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x""))," & RngTarget.Row - RngTable.Row + 1 & ")-0," & Split(StrColumnsIndex, ";")(RngTarget.Column - RngTable.Column) * 1 & "),"""")")
'If RngTarget contains nothing then it's assumed there are no more results to be reported and the macro is terminated.
If RngTarget.Value = "" Then Exit Sub
Next
End Sub
谢谢。我实现了它，它适用于一行。如果我想从主表中添加下一个数据集，这只会重复前一行的内容。我怎样才能让它列出超过一行的汇总数据呢?
预期结果:它选择相关的数据行并列出它(根据查找标准的不同数据)
实际结果:它只选择一行，并在第二行
中重复它。现在我定义了以下代码:
Sub Button1_Click()
Cells(33, 1) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,1),"""")")
Cells(33, 2) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,3),"""")")
Cells(33, 3) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,5),"""")")
Cells(33, 4) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,7),"""")")
Cells(33, 5) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,8),"""")")
Cells(33, 6) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,9),"""")")
Cells(33, 7) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,10),"""")")
Cells(33, 8) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,11),"""")")
Cells(33, 9) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,14),"""")")
Cells(33, 10) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,15),"""")")
Cells(33, 11) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,16),"""")")
Cells(34, 1) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,1),"""")")
Cells(34, 2) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,3),"""")")
Cells(34, 3) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,5),"""")")
Cells(34, 4) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,7),"""")")
Cells(34, 5) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,8),"""")")
Cells(34, 6) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,9),"""")")
Cells(34, 7) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,10),"""")")
Cells(34, 8) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,11),"""")")
Cells(34, 9) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,14),"""")")
Cells(34, 10) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,15),"""")")
Cells(34, 11) = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x"")),ROW()-32)-0,16),"""")")
结束子
下面是主表Endkontrolle":
中的数据示例<表类>日期 产品 员工 … 日期范围 22.04.2022 MOTI AKAH … x 23.04.2022 MOTI_BG AKAH … x 26.04.2022 MOTI AKAH …