vba 在宏的公式中使用输入变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14038324/
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
Use an input variable inside a formula in macro
提问by Mehper C. Palavuzlar
I want to use a user input inside a formula as follows:
我想在公式中使用用户输入，如下所示：
Sub example()
Dim StockDays As Integer
StockDays = InputBox(Prompt:="How many days?")
Range("AG2").FormulaR1C1 = "=ROUNDUP(RC[-6]*" & StockDays & "/90, 0)"
Range("AG2").Select
Selection.AutoFill Destination:=Range(Cells(2, 33), Cells(1500, 33))
End Sub
When run, the above code throws an error at the ROUNDUP line.
运行时，上面的代码在 ROUNDUP 行抛出错误。
Run-time error 1004.
Application-defined or object-defined error.
运行时错误 1004。
应用程序定义或对象定义的错误。
I think the problem is related to the variable StockDays.
我认为问题与变量有关StockDays。
How can I arrange the code so that I can make it work?
我该如何安排代码才能使其工作？
回答by Siddharth Rout
I have commented the code so you shouldn't have any problem understanding it :)
我已经评论了代码，所以你理解它应该没有任何问题:)
Option Explicit
Sub example()
Dim StockDays As Integer
'~~> Type:=1 will ensure that the user enters only numbers
StockDays = Application.InputBox(Prompt:="How many days?", Type:=1)
'~~> No Need to autofill. You can fill all the range in one go
Thisworkbook.Sheets("Sheet1").Range("AG2:AG1500").FormulaR1C1 = _
"=ROUNDUP(RC[-6] * " & StockDays & "/ 90, 0)"
'OR this as mentioned in your comment
Thisworkbook.Sheets("Sheet1").Range("AG2:AG1500").FormulaR1C1 = _
"=ROUNDUP((RC[-6]* " & StockDays & "/90),0)"
End Sub
回答by Naveen Babu
I think this should work
我认为这应该有效
Range("AG2").FormulaR1C1 = "=ROUNDUP(RC[-6] * " & StockDays & "/ 90, 0)"
Range("AG2").FormulaR1C1 = "=ROUNDUP(RC[-6] * " & StockDays & "/ 90, 0)"
the mistake u did was, you where applying a formula but forgot to replace the variable u used with the value in the varaiable
您犯的错误是，您在应用公式时忘记将您使用的变量替换为变量中的值
