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.
The user input date was 9/30/2013, which I stored in 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$20,""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.
Thanks
