Problem with DateAdd function in a Do Loop

Question

Hi all,

I'm not very experienced and was wondering if you could help me with the DataAdd function.  I'm trying to count forward one month on columns that are offset but when I run the below code I keep getting dates different to what I expect e.g. 01/01/1900 instead of 01/01/2010.  It did work if I use the VBA function Date for my "Start" variable but at the moment I want the user to define it from a text box or userform.   I'm not sure if its something I'm defining wrongly or I'm trying to match variables incorrectly, hope you can help. 

Please see my code for this below: 

Private Sub SDateButton_Click()

Dim Start As Double, Current As Double, Final As Double

Unload ActionForm

On Error Resume Next:

Start = StartDate.Value

Cells(1, 5).Select

Selection = Start

Current = DateAdd("m", 1, Start)

Final = DateAdd("yyyy", 3, Start)

Do Until Current = Final

ActiveCell.Offset(0, 4).Select

ActiveCell = Current

Current = DateAdd("m", 1, Current)

Loop

Range("E1:IV1").NumberFormat = "dd/mm/yyyy"

End Sub

---

Regards, Brett

Answer 1

Hey Rick,

I've been trawling Answers to save picking your brains all the time and I found something by "Gary's Student" that did the trick, as per below (even test's if the user is typing the date incorrectly, ace!). I've put the new additions in bold but I'm sure its obvious to you...

Private Sub SDateButton_Click()

Dim Current As String, Final As String

Dim d As Date

On Error GoTo incorrect:

d = DateValue(StartDate)

Unload ActionForm

Cells(1, 5).Select

Selection = d

Current = DateAdd("m", 1, d)

Final = DateAdd("yyyy", 3, d)

Do Until Current = Final

ActiveCell.Offset(0, 4).Select

ActiveCell = DateValue(Current)

Current = DateAdd("m", 1, Current)

Loop

Exit Subincorrect:

MsgBox ("Incorrect Date Value!")

End Sub

Thanks for your help previously, your always there to help and it is much appreciated! 

---

Regards, Brett

Answer 2

I keep getting dates different to what I expect e.g. 01/01/1900 instead of 01/01/2010.

This means that no date is being assigned to your Start variable, so you will need to start your investigation here...

Start = StartDate.Value

What is StartDate... your TextBox on the ActionForm UserForm? If so, maybe your problem is you Unload'ed the UserForm before you grabbed the value from the TextBox. Try moving the above code line in front of this line...

Unload ActionForm

and see if that makes a difference.

Answer 3

Thanks Rick,

That worked!  I didn't realise that it had to set the value first, instead I assumed that it held the value in memory... a newbie mistake!

Not to be a pain.  I now have the original issue I seen where my dates are coming out as "MM/DD/YYYY".  I've tried reformatting these so it shows as "DD/MM/YYYY"  with the below line at the end of the above code:

Range("E1:IV1").NumberFormat = "dd/mm/yyyy"

The problem is it still shows as "MM/DD/YYYY" on the sheet.  Like I mentioned above this wasn't a problem when I used date serial instead of text (sorry my Dims above should of all been string).  Do you know what might be going wrong?

Hope you can help...

---

Regards, Brett