vba VBA简单的宏来复制公式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19404203/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA simple macro to copy formulas
提问by Filip Matyja
I would like to copy formulas from column into row. Formulas don't have $ signature in address so I cannot use special paste options, because it will change address in formulas of each cell.
我想将公式从列复制到行中。公式在地址中没有 $ 签名，因此我不能使用特殊的粘贴选项，因为它会更改每个单元格公式中的地址。
I try to run macro, but it copy only last cell from column to everyone one in row.
我尝试运行宏，但它只将列中的最后一个单元格复制到一行中的每个人。
Sub Zamiana()
For Each y In Range("A1:B1")
For Each X In Range("A2:A3")
y.Formula = X.Formula
Next
Next
End Sub
Thanks in advance for help :)
预先感谢您的帮助:)
回答by chris neilsen
A simple method is to transfer the formulas via a variant array and the transposefunction.
一种简单的方法是通过变体数组和transpose函数传输公式。
Like this:
像这样：
Sub TransposeFormulas()
Dim rSrc As Range
Dim rDst As Range
Dim vSrc As Variant
Set rSrc = Range("A1:B1")
Set rDst = Range("A2") ' top left cell of destination
vSrc = rSrc.Formula ' copy source formulas into a variant array
' size the destination range and transpose formulas into it
rDst.Resize(UBound(vSrc, 2), UBound(vSrc, 1)).Formula = _
Application.Transpose(vSrc)
End Sub
回答by udigold
try adding two indexes, one for column and one for row and dropping a for loop, something like this
尝试添加两个索引，一个用于列，一个用于行，然后删除一个 for 循环，就像这样
Sub Zamiana()
iR = 1
iC = 0
For Each y In Range("A1:B1")
r = y.Row + iR
c = y.Column - iC
Cells(r, c).Formula = y.Formula
iR = iR + 1
iC = iC + 1
Next
End Sub
