Looping through list of dates to display datediff

Question

I am trying to create a macro that as well as many other things will loop through the dates in column and, compare it to the "due date" and show the amount of dates that is left

Unfortunately the macro that I wrote doesn't give the desired outcome and gives me some huge number instead.

my code for that part so far is:

Sub calcDays()

Dim dueDate As Date
Dim macroLastRow As Long
Dim macroWs As Worksheet


Set macroWs = ThisWorkbook.Worksheets("Macro")

macroLastRow = macroWs.Range("A" & Rows.Count).End(xlUp).Row
dueDate = Range("B1").Value


For i = 2 To macroLastRow

Cells(i, 2).Value = DateDiff("d", i, dueDate)

Next i



End Sub

This is what I get after I run the macro:

I hope that you guys will be able to advise! Thanks a mil in advance!

Number you get is a date 02/02/2020 formatted as number. What is `invoiceDate` in your code? — Egan Wolf, Jan 21 '20 at 11:47
You never declare or define `InvoiceDate`. Since you did not include `Option Explicit`, that error is not flagged, and `InvoiceDate` becomes a `0`. Hence your results. — Ron Rosenfeld, Jan 21 '20 at 11:48
Tahnks, I was trying to define it, in the loop, but deleted it since I didn't get the result and forgot to change it before copying the code. — Sergej Dikun, Jan 21 '20 at 11:49
I've edited the code now, it's the i that it should take as a value in the loop. With regards of the date in B1, I've checked and it's set as a date in formatting. — Sergej Dikun, Jan 21 '20 at 11:51
There is no value assigned to `invoiceDate`. You may replace it with `Cells(i, 1).Value` and that will do the trick. I also would recommend you to switch on the setting "Require Variable Declaration". You may find the way to do it [here in the answer](https://stackoverflow.com/questions/59474793/run-time-error-1004-on-my-vba-excel-code-transfer-data-from-one-worksheet-to-a/59479095#59479095) — Vitaliy Prushak, Jan 21 '20 at 11:51
that's it! That's what was wrong with it, thank you Vitaliy! I only added i assuming that it will take the value since the loop goes through column 1 anyway. — Sergej Dikun, Jan 21 '20 at 11:54
Loop does not go through column 1. It goes from number 2 to some number stored in variable macroLastRow. This is not connected to column 1. — Egan Wolf, Jan 21 '20 at 11:58
Thanks Egan. macroLastRow has column A as it's value to seek for last row. But anyway, this works now :-) — Sergej Dikun, Jan 21 '20 at 11:59
@VitaliyPrushak can you add your comment as an answer, I will accept it. It fixed the issue and all works now just fine :-) — Sergej Dikun, Jan 21 '20 at 12:12

score 1 · Accepted Answer · answered Jan 21 '20 at 12:14

There is no value assigned to invoiceDate. You may replace it with Cells(i, 1).Value and that will do the trick.
I also would recommend you to switch on the setting "Require Variable Declaration". You may find the way to do it here in the answer.

Also, discard my edit to your post for problem to remain.

Looping through list of dates to display datediff

1 Answers1