Ed has attended:
Excel VBA Introduction course
Excel VBA Intermediate course
Excel Advanced course
Access Introduction course
Effective Communication Skills course
Do Until Loop in VBA
Hi,
I'm trying to write a DO Loop in VBA.
The aim is essentially to add the month end date for each month going forward in time until a set date is reached. The code I have so far is:
Dim BeginPos As Integer
Dim LastDate As Date
Do Until LastDate = ThisWorkbook.Sheets("Input").Range("C9")
LastDate = Selection
BeginPos = Range("A" & Rows.count).End(xlUp).Row
Range("A" & BeginPos + 1).Select
Selection.FormulaR1C1 = "=eomonth(R[-1]C1+1,0)"
Loop
Basically I want the code to look down column A and when it gets to the end, enter the next month end date in the blank cell. I want this to be repeated until it gets to a date equal to some reference (in this case that reference is on the "input" sheet).
Would you be able to advise where I'm going wrong?
Thanks,
Ed
RE: Do Until Loop in VBA
Hi Ed
Thanks for question.
Your code seems to work fine. Though it does create an extra month end date at the end. Is that what you found?
Also the dates are not date formatted.
Here's yours with a couple of changes
Dim BeginPos As Integer
Dim LastDate As Date
Do Until LastDate = ThisWorkbook.Sheets("Input").Range("C9")
BeginPos = Range("A" & Rows.Count).End(xlUp).Row
Range("A" & BeginPos + 1).Select
Selection.FormulaR1C1 = "=eomonth(R[-1]C1+1,0)"
Selection = DateValue(Format(Selection, "dd/mm/yy"))
LastDate = Selection
Loop
Notes
Moving the line LastDate = Selection to the end of the loop ensures you don't get an extra month.
The line starting Selection = Datevalue(..
formats the dates.
You have to type the start date eg 31/01/17 into the first selected cell then run the macro.
Your macro is similar to dragging to Autofilling an end of month date and then choosing Fill Months.
Hope this helps!
Regards
Doug
STL
RE: Do Until Loop in VBA
Thanks Doug that works well now. The one I did wouldn't stop - it just kept on adding dates.
I think it was the formatting that helped.
Thanks again,
Ed
RE: Do Until Loop in VBA
That's good!
There has to be a date typed in the A column first then it should work.
Glad that helped.
Doug
