时间戳

首页 > 解决方案 > VBA ArrayFormula 太长

excel - VBA ArrayFormula 太长

问题描述

我一直在尝试使用 vba 插入数组公式但它太长了，所以我使用替换函数将它拆分。但是替换仍然不会相应地更改公式。

任何帮助将非常感激。

这是我最初的工作公式：{=IF(IFERROR(INDEX($F:$F;AGGREGATE(15;6;ROW($C$2:$C$1203)/($C$2:$C$1203=$J2);COLUMNS($F$1:F$1)));"")&" "&IFERROR(INDEX($E:$E;AGGREGATE(15;6;ROW($C$2:$C$1203)/($C$2:$C$1203=$J2);COLUMNS($E$1:E$1)));"")=$F2&" "&$E2;"dieselbe Person";IFERROR(VLOOKUP(SUBSTITUTE(SUBSTITUTE(IFERROR(INDEX($E:$E;AGGREGATE(15;6;ROW($C$2:$C$1203)/($C$2:$C$1203=$J2);COLUMNS($E$1:E$1)));"")&" "&IFERROR(INDEX($F:$F;AGGREGATE(15;6;ROW($C$2:$C$1203)/($C$2:$C$1203=$J2);COLUMNS($E$1:E$1)));"");"ß";"ss");"ö";"oe");Sheet2!$A:$B;2;FALSE);""))}

这是相应的VBA代码：

Sub Macro1()

Dim sht As Worksheet


Dim formulapart2 As String

Dim formulapart3 As String

Dim rng As Range

shtName = InputBox("Respective Sheetname:")

Set sht = ThisWorkbook.Sheets(shtName)

formulapart2 = "INDEX(C6,AGGREGATE(15,6,ROW(R2C3:R1203C3)/(R2C3:R1203C3=RC10),COLUMNS(R1C6:R1C[-6])))"

formulapart3 = "INDEX(C5,AGGREGATE(15,6,ROW(R2C3:R1203C3)/(R2C3:R1203C3=RC10),COLUMNS(R1C5:R1C[-7])))"

Set rng = sht.Range("L2:O11")

rng.FormulaArray = "=IF(IFERROR(""F2"","""")&"" ""&IFERROR(""F3"","""")=RC6&"" ""&RC5,""dieselbe Person"",IFERROR(VLOOKUP(SUBSTITUTE(SUBSTITUTE(IFERROR(""F4"","""")&"" ""&IFERROR(""F5"",""""),""ß"",""ss""),""ö"",""oe""),Sheet2!C1:C2,2,FALSE),""""))"

rng.Replace """F2""", formulapart2, xlPart

rng.Replace """F3""", formulapart3, xlPart

rng.Replace """F4""", formulapart3, xlPart

rng.Replace """F5""", formulapart3, xlPart


End Sub

实际上这段代码不会产生任何错误，但替换函数不会改变公式。

标签： excelvba

解决方案

这里的问题是您尝试传递的公式部分使用 R1C1 表示法表示，而已经在该范围内的公式可能用 A1 表示法表示。

我使用“可能”这个词，因为这很可能是您的 Excel 公式的设置，所以在运行该行之后

rng.FormulaArray = "=IF(IFERROR(""F2"","""")&"" ""&IFERROR(""F3"","""")=RC6&"" ""&RC5,""dieselbe Person"",IFERROR(VLOOKUP(SUBSTITUTE(SUBSTITUTE(IFERROR(""F4"","""")&"" ""&IFERROR(""F5"",""""),""ß"",""ss""),""ö"",""oe""),Sheet2!C1:C2,2,FALSE),""""))"

该公式将转换为 A1 样式的公式，它会阻止您的Replace方法按您期望的方式工作。

解决此问题的一种方法是在代码请求时将引用样式设置为 R1C1，并在最后恢复为 A1，如下所示：

Application.ReferenceStyle = xlR1C1

' You code

Application.ReferenceStyle = xlA1

推荐阅读