Excel VBA 使用公式创建新列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19956524/
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
Excel VBA creating a new column with formula
提问by Amatya
I have an excel file with a column which has date data. I want the user to input a date of their choosing and then I want to create a new column that lists the difference in days between the two dates. The Macro that I have is working but I have a few questions and I would like to make it better. Link to MWE small data file is here.
我有一个包含日期数据的列的 excel 文件。我希望用户输入他们选择的日期，然后我想创建一个新列，列出两个日期之间的天数差异。我拥有的宏正在运行，但我有几个问题，我想让它变得更好。链接到 MWE 小数据文件在这里。
The user input date was 9/30/2013, which I stored in H20
用户输入日期是 9/30/2013，我存储在 H20
Macro:
宏：
Sub Date_play()
Dim x As Date
Dim x2 As Date
Dim y As Variant
x = InputBox(Prompt:="Please enter the Folder Report Date. The following formats are acceptable: 4 1 2013 or April 1 2013 or 4/1/2013")
x2 = Range("E2")
y = DateDiff("D", x2, x)
MsgBox y
'Used DateDiff above and it works but I don't know how to use it to fill a column or indeed a cell.
Range("H20").FormulaR1C1 = x
Range("H1").FormulaR1C1 = "Diff"
Range("H2").Formula = "=DATEDIF(E2,$H,""D"")"
Range("H2").AutoFill Destination:=Range("H2:H17")
Range("H2:H17").Select
End Sub
Now, could I have done this without storing the user input date in a particular cell? I would've preferred to use the variable "x" in the formula but it wasn't working for me. I had to store the user input in H20 and then use $H$20.
What's the difference between the function Datedif and the procedure DateDiff? I am able to use the procedure DateDiff in my macro but I don't know how to use it to fill out my column. Is one method better than the other?
Is there a better way to add columns to the existing sheet, where the columns include some calculations involving existing data on the sheet and some user inputs? There are tons of more complicated calculations I want to do next.
现在，我可以在不将用户输入日期存储在特定单元格中的情况下完成此操作吗？我更喜欢在公式中使用变量“x”，但它对我不起作用。我不得不将用户输入存储在 H20 中，然后使用 $H$20。
函数Datedif 和过程DateDiff 有什么区别？我可以在我的宏中使用 DateDiff 过程，但我不知道如何使用它来填写我的专栏。一种方法比另一种更好吗？
是否有更好的方法向现有工作表添加列，其中列包括一些涉及工作表上现有数据和一些用户输入的计算？我接下来要做大量更复杂的计算。
Thanks
谢谢
采纳答案by Siddharth Rout
Q1.Now, could I have done this without storing the user input date in a particular cell? I would've preferred to use the variable "x" in the formula but it wasn't working for me. I had to store the user input in H20 and then use $H$20.
一季度。现在，我可以在不将用户输入日期存储在特定单元格中的情况下完成此操作吗？我更喜欢在公式中使用变量“x”，但它对我不起作用。我不得不将用户输入存储在 H20 中，然后使用 $H$20。
Try this (UNTESTED)
试试这个（未经测试）
Replace
代替
Range("H20").FormulaR1C1 = x
Range("H1").FormulaR1C1 = "Diff"
Range("H2").Formula = "=DATEDIF(E2,$H,""D"")"
Range("H2").AutoFill Destination:=Range("H2:H17")
Range("H2:H17").Select
by
经过
Range("H2:H17").Formula = "=DATEDIF(E2," & datevalue(x) & ",""D"")"
The above will fill all the cells with the formula in one go. You do not need the Autofill to do the job. Also Inputbox is the worst choice to accept dates. You might want to see THIS
以上将一次性使用公式填充所有单元格。您不需要Autofill to do the job. 此外，输入框是接受日期的最糟糕选择。你可能想看这个
Q2.What's the difference between the function Datedif and the procedure DateDiff? I am able to use the procedure DateDiff in my macro but I don't know how to use it to fill out my column. Is one method better than the other?
Q2。函数Datedif 和过程DateDiff 有什么区别？我可以在我的宏中使用 DateDiff 过程，但我不知道如何使用它来填写我的专栏。一种方法比另一种更好吗？
DATEDIF is a worksheet function and DateDiff is a VBA Function.
DATEDIF 是一个工作表函数，DateDiff 是一个 VBA 函数。
