Subtraction of Dates using macros

Question

In myexcel I wanted to calculate the difference in days between a Single Date in a cell and a series of Dates in column AS2:AS400

Also the difference in days i want it in a seperate column.

I tried to understand the code via record macro and modify it to a two line code. But I couldnt not make it. Could anyone simplyfy the below code to simplfied program. So that I can use for a large no. of data very easily in a column instead of writing it for every single cell.

Range("AS2").Select
ActiveCell.FormulaR1C1 = "=R[3]C[-42]-RC[-22]"
Range("AS3").Select
ActiveCell.FormulaR1C1 = "=R[2]C[-42]-RC[-22]"
Range("AS4").Select
ActiveCell.FormulaR1C1 = "=R[1]C[-42]-RC[-22]"
Range("AS5").Select
ActiveCell.FormulaR1C1 = "=RC[-42]-RC[-22]"
Range("AS6").Select
ActiveCell.FormulaR1C1 = "=R[-1]C[-42]-RC[-22]"
Range("AS7").Select
ActiveCell.FormulaR1C1 = "=R[-2]C[-42]-RC[-22]"
Range("AS8").Select
ActiveCell.FormulaR1C1 = "=R[-3]C[-42]-RC[-22]"

Just because I see that you used a lot of `.Select` which is a bad practice and should be avoided. You might read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) and your code style and performance will get a lot better. — Pᴇʜ, Mar 14 '18 at 15:24
Actually I am beginner. This is code from Record Micro. I could not modify completely into a complete program. — sheela, Mar 14 '18 at 15:27
That's ok, just a recommendation to read that especially for beginners. (did you see the edited answer below?) — Pᴇʜ, Mar 14 '18 at 15:29

Pᴇʜ · Accepted Answer · 2018-03-14T15:19:36.590

Have a look at the documentation of the DateDiff Function:

For example:

Range("BA2").Value = DateDiff("d", Range("C5").Value,  Range("AS2").Value)

… will return the difference of C5 and AS2 in days into BA2.

But you can do that without VBA too:

Put the following formula into BA2 =DAYS(C5,AS2) to get the difference in days.

Or you can write that formula with VBA into the cell:

Range("BA2").Formula = "=DAYS(C5,AS2)"

For writing multiple formulas in a loop:

Dim i As Long
For i = 2 to 5
    Range("BA" & i).Formula = "=DAYS(C5,AS" & i &")"
Next i

Or even better just write the first cell and copy it down

Range("BA2").Formula = "=DAYS($C$5,AS2)" 'write first formula
Range("BA2").AutoFill Destination:=Range("BA2:BA5"), Type:=xlFillDefault 'copy it down until BA5

Note that $C$5 is a fixed cell but AS2 will adjust dynamically when you copy down.

@peh but the aim is not only for BA2. it is for a series of cells. Example: BA2=C5-AS2; BA3=C5-AS3; BA4=C5-AS4; BA5=C5-AS5 — sheela, Mar 14 '18 at 15:18
@sheela see my edit. Probably the last one is the one to go for you. — Pᴇʜ, Mar 14 '18 at 15:19

Subtraction of Dates using macros

1 Answers1