Error in VBA code, looking to get end of month dates from start date to end date

[frnd4kumar]

Hi I am not able to progress after getting output got cell J11. Looks like the loop isn't working. I am looking to get the month end dates in the row 11 from the start date to end date. Can any one please help me to resolve this?

 

[Peter_SSs]

A few tips for getting the best help in the forum.

1.
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

2.
Helpers cannot copy sample data from an image to test with. MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

3.
When asking for help with existing vba code, copy/paste the actual code, not an image of it. As with the sample data, helpers cannot copy the vba from an image. My signature block below has more information about the best way to post your vba code.

 

[Alex Blakenburg]

Your loop currently reads like this:

For x = 11 to 10  'Where 10 is the value in c which is the value in E9

There are a couple of issues.
1) 10 months is not nearly enough to get you from the start date to the end date
2) your loop is meant to go from Column 11 and increment for the next 10 mths taking you out to 20

You are probably better off using Fill Series but see if this helps you with the loop method.
(It is not using E9 but calculating the months in the code based on E7 and E8)

Sub ModelTiming()
    Dim myStart As Date
    Dim myEnd As Date
    Dim noOfMths As Long
    Dim colStart As Long
    Dim x As Long
    
    myStart = Range("E7")
    myEnd = Range("E8")
    noOfMths = DateDiff("m", myStart, myEnd)
    Cells(11, "J") = Application.WorksheetFunction.EoMonth(myStart, 0)
    colStart = Cells(11, "J").Column + 1

    For x = colStart To (colStart + noOfMths - 1)
        Cells(11, x) = Application.WorksheetFunction.EoMonth(Cells(11, x - 1), 1)
    Next x
    
    Cells(11, colStart - 1).Resize(, noOfMths + 1).NumberFormat = Range("E7").NumberFormat
End Sub

20240115 VBA Fill Months frnd4kumar.xlsm
	D	E	F	G	H	I	J
5	Code Set up
6
7	Start Date	1/01/2015
8	End Date	31/12/2023
9	Period	10
10
11							31/01/2015
Sheet1