Excel VBA creating a new column with formula

Question

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

What error were you getting when you tried to use `x` in the formula? — ARich, Nov 13 '13 at 14:48
in H20 you're not asking for a formula but for a simple date, so better use `[H20] = x` ... same for [H1] — MikeD, Nov 13 '13 at 14:59
@ARich when I use x in the formula, I don't get an error message, I just get #NAME? in the columns. When I do the trace error thing, I get to an invalid name error. — Amatya, Nov 13 '13 at 15:05
@SiddharthRout Let me ask in another question. I am trying to do slightly more complicated calculations in the newly created row. — Amatya, Nov 13 '13 at 16:40
`I am trying to do slightly more complicated calculations in the newly created row.` @Amatya: Row? or Column? — Siddharth Rout, Nov 13 '13 at 16:41

score 1 · Accepted Answer · edited May 23 '17 at 11:59

1

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.

Try this (UNTESTED)

Replace

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

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

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?

DATEDIF is a worksheet function and DateDiff is a VBA Function.

edited May 23 '17 at 11:59

Community

1
1

answered Nov 13 '13 at 15:51

Siddharth Rout

147,039
17
206
250

Hey Siddharth, datevalue(x) is not working. I am getting #NUM! in the cells and the formula for the cell says =DATEDIF(E2, 8/25/13,"D") if my x was 8/25/13 – Amatya Nov 14 '13 at 15:43
I just tested it and it works just fine. What happens if you put x as "25/8/13"? – Siddharth Rout Nov 14 '13 at 16:15
If I accept x in the InputBox as "8/25/13" then I get a mismatch because I have DIM x As Date. If I change x to String, and then accept x in InputBox as "8/25/13" then I am getting an error and #NUM! in the cells. – Amatya Nov 14 '13 at 16:26
for me it works both ways. Did you see the link in my answer on how to accept dates? – Siddharth Rout Nov 14 '13 at 16:29
I totally missed the link. I'll check it out and respond. Thanks! :) – Amatya Nov 14 '13 at 16:31

Excel VBA creating a new column with formula

1 Answers1