vba 将公式复制到一系列单元格并将其转换为值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20969386/
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
Copying formula to a range of cells and converting it to a value
提问by Vidhi
I am trying to copy the formula from D1 and paste it till last row(26446) value may increase
我正在尝试从 D1 复制公式并将其粘贴到最后一行（26446）值可能会增加
Dim lrow As Double
lrow = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
Range("D2").Copy Destination:=Range("D2:D" & lrow)
Range("D2" & "lrow").Copy 'THIS LINE SHOWS 1004 error
Range("D2:D" & "lrow").PasteSpecial Paste:=xlPasteValues
How to solve this
如何解决这个问题
采纳答案by Siddharth Rout
You have already got two excellent answers and hence this is not an answer but an enhancement to your code.
您已经得到了两个很好的答案，因此这不是答案，而是对您的代码的增强。
You don't need to declare the row variable as
Double.Longis good enough. You may want to read up on Data type HEREYour code can be reduced to this
您不需要将行变量声明为
Double.Long足够好。您可能想在这里阅读数据类型你的代码可以减少到这个
Code
代码
BTW, Your question says D1but your code is taking D2into consideration. So I am going with D2. If it is D1then replace D2by D1below.
顺便说一句，你的问题说，D1但你的代码正在D2考虑。所以我和D2. 如果是，则由下面D1替换。D2D1
Sub Sample()
Dim lrow As Long
With Sheets("Sheet2")
lrow = .Range("A" & .Rows.Count).End(xlUp).Row
.Range("D2:D" & lrow).Formula = .Range("D2").Formula
.Range("E2:E" & lrow).Formula = .Range("E2").Formula
.Range("F2:F" & lrow).Formula = .Range("F2").Formula
.Range("G2:G" & lrow).Formula = .Range("G2").Formula
.Range("D2:G" & lrow).Value = .Range("D2:G" & lrow).Value
End With
End Sub
回答by Pradeep Kumar
Anything within quotes will be considered as a string so "lrow" is considered as a Stringand not a variable.
引号内的任何内容都将被视为字符串，因此“lrow”被视为一个String而不是变量。
It should be
它应该是
Range("D" & lrow).Copy
or whatever range you are trying to copy. I think, you are trying
或您尝试复制的任何范围。我想，你正在努力
Range("D2:D" & lrow).Copy
The other simple alternative to
另一个简单的替代方案
Range("D2:D" & "lrow").Copy
Range("D2:D" & "lrow").PasteSpecial Paste:=xlPasteValues
is
是
Range("D2:D" & lrow).Value = Range("D2:D" & lrow).Value
