Excel VBA - 将垂直数组中的公式转置为水平数组

Question

我已经用各种短语在谷歌上搜索了我的问题，但是我很难找到一个可行的方法。这可能需要结合几个不同的解决方案，或者一种我尚未考虑过的方法;所以任何帮助都将不胜感激。

假设我有公式分别在单元格A1、A2、A3和A4中。假设我想把这些精确的公式向右移动一列。

在VBA中，我可以说:

Range("B1:B4").Formula = Range("A1:A4").Formula

我希望你能够做如下事情：

Range("B1:E1").Formula = Range("A1:A4").Formula

看看我的B:E范围是水平的，而A1:A4的范围是垂直的。

我尝试了各种转置选项，但都找不到适用的，因为我想要精确的公式转移。

有什么想法吗？

- J Taylor

---

所以你正在寻找一种复制和粘贴转置公式的方法，但保留相对（而不是“=$A$1”）引用？ - seadoggie01

@seadoggie - 实际上恰恰相反。我需要复制/粘贴保留公式，但我的计划是使用LastRow解决方法将该行公式复制到数据底部。 - J Taylor

@J Taylor - 如果您不关心相对引用，那么可以使用复制和粘贴特殊功能。选择粘贴公式并勾选转置选项。 - seadoggie01

Answer 1

你可以尝试类似以下的代码：

Sub PivotRangeFormulas()
    Dim rngSrc As Range: Set rngSrc = ActiveSheet.Range("A1:A4")
    Dim rngTgt As Range: Set rngTgt = ActiveSheet.Range("B1:E1")
    Dim i As Long: For i = 1 To rngSrc.Rows.Count
        Application.Index(rngTgt, i).Formula = Application.Index(rngSrc, i).Formula
    Next i
End Sub

您还可以从每个区域的第一个单元格使用Offset函数

- Tragamor

---

我并不是完全的VBA初学者，但我也不是程序员/开发人员（我从事报告和数据分析），因此相比其他人来说，阅读和理解这个需要更长的时间。话虽如此，我认为我明白你在这里做什么。我不熟悉application.index的引用，但我大致了解它的意思。我今天/明天会尝试一下，并汇报结果。 - J Taylor

所以实际上，这个方法可行。不过奇怪的是，当我开始输入“Application.Index”时，没有弹出帮助对话框，让我认为“Application.Index”不是一个有效的函数。尽管如此，这个方法完美地解决了问题。非常感谢！ - J Taylor

Answer 2

Range("B1:E1").Formula = WorksheetFunction.Transpose(Range("A1:A4").Formula)

- user3598756

Answer 3

在公式中锁定单元格引用是否可行？我相信你已经知道，但是 F4 键（PC）可以切换引用单元格的锁定。美元符号锁定列字母或行号 [A6、$A$6、A$6、$A6]。如果锁定单元格引用，则可以复制和转置公式。

Range("B1:E1").Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=True

这里还有另一个选项：尝试在输入公式时录制宏。如果您的公式是：

=SUM(D3:D4) 

根据您输入公式的位置，VBA输出可能如下：

"=SUM(R[3]C:R[4]C)"

这是VBA中的绝对引用：

"=SUM(R3C4:R4C4)"

你可以像这样做：

然后你可以做一些事情：

Range("A8:A23").FormulaR1C1 = "=SUM(R3C4:R4C4)"

这将在所有单元格"A8:A23"中输入公式=SUM($D$3:$D$4)。如果您在VBA公式中尝试括号，应该能够使其正常工作。下面的公式搜索包含文本“nff”的所选单元格左侧的列：

    Selection.FormulaR1C1 = _
    "=SEARCH(""nff"",RC[-1])"

- Bo_

---

@Bo - 是的，这是可能的，但我的代码的下一步将是将这些公式拖到数据底部，因此当它们进入时，我需要引用是相对的。我想我可以使它们绝对，使用复制/粘贴转置功能，然后通过另一行VBA代码在新位置再次使它们变成相对的... - J Taylor