Fill missing dates in a sequence from the 1st of the month until the last day of the month

Hi,

I am have a spread sheet that has dates in "B" and ranges to "F" with other data elements. The data provided in the Date column only accounts for workdays, however I need to have the all the dates of the month that I am working on. I am working on a code that works fine until I get towards the end of the month (e.g. the last date provided is 03/28/13 but I also need the code to add in dates after the last date provided meaning 03/29/13, 03/30/13, 03/31/13. This is the code that I have so far:

Sub ABC()
Dim rw As Long, colDate As String, colDay As String
Dim colLocation As String
rw = 2
colDate = "B"
Do
If Cells(rw + 1, colDate) = "" Or Cells(rw, colDate) = "" Then
rw = rw + 1
Else
If Cells(rw, colDate) < Cells(rw + 1, colDate).Value - 1 Then
Rows(rw + 1).EntireRow.Insert
Cells(rw + 1, colDate).Value = Cells(rw, colDate) + 1
rw = rw + 1
Else
rw = rw + 1
End If
End If
If rw > Cells(Rows.Count, colDate).End(xlUp).Row Then Exit Do
Loop
End Sub

How can I make it complete the task until the end of the month insted of just until the last day provided by the sheet? Can someone help me out with this?

Best

Kaygu

It also should be possible to use the Excel function "EoMonth" i.e.

= WorksheetFunction.EoMonth(startdate, 0)

Alf

Hi Alf,

Thank you for your reply. How would I add your code into mine so i would get the desired result. This is my first time writing in VBA and I have no experience. Could you help me out to get the complete code together?

Thanks

Kaygu

Perhaps like this?

Do Until Cells(rw, ColDate) = WorksheetFunction.EoMonth(startdate, 0)

Is startdate found in B2? If so replace startdate with

Range("B2)

Alf

Thanks for explaining further. I am just having a bit of trouble on where within my code to put your EoMonth function or what code to replace with it. My code now looks like this but it does not work. I get "compile error: Else without If" error message when trying to run the macro.

Sub ABC()
Dim rw As Long, colDate As String, colDay As String
Dim colLocation As String
rw = 2
colDate = "B"
Do
If Cells(rw + 1, colDate) = "" Or Cells(rw, colDate) = "" Then
rw = rw + 1
Do Until Cells(rw, colDate) = WorksheetFunction.EoMonth("B2", 0)
Else
If Cells(rw, colDate) < Cells(rw + 1, colDate).Value - 1 Then
Rows(rw + 1).EntireRow.insert
Cells(rw + 1, colDate).Value = Cells(rw, colDate) + 1
rw = rw + 1
Else
rw = rw + 1
End If
End If
If rw > Cells(Rows.Count, colDate).End(xlUp).Row Then Exit Do
Loop
End Sub

kaygu

I would try

Do Until Cells(rw, colDate) = WorksheetFunction.EoMonth(Range("B2"), 0)

and block line

If rw > Cells(Rows.Count, colDate).End(xlUp).Row Then Exit Do

If this does not work perhaps you could upload the wookbook?

Alf

Hi,

I can't get it to work. I have attached a workbook with a sheet for before and how it should look after the macro has run. Hope this gives you more insight.

Thanks

Ok here we go.

Run macro "AddDate" and see if this is what you wanted.

Alf

Thank you very much the macro works great. I have one last thing. I need to macro to perform the same task for multiple date ranges within the column until all of the dates are done (e.g. in my orginial file i have up to 50 times the same date under different stocks. I sort the data for stocks and then date, so that the dates are in order for each indiviual stock, then I would run your macro to fill in the missing dates. So the macro needs to do the task over again until all date gaps are filled). Would that be possible? That would be the biggest time saver for me.

Thanks

Kaygu

so that the dates are in order for each indiviual stock, then I would run your macro to fill in the missing dates

I think that should be possible but could you upload another sample file where I can see the layout in order to modify the macro?

Then I wonder, at the moment this macro starts by checking the last date and add the end of the month date if this is missing. How about the start date i.e. the 1st of the month? This could just as easily being checked and added if missing.

Alf

Thanks Alf.

I have attached another sample file. The dates that are missing for each stock can be different dates and in this sample file I just show for example 2 stocks there would be more coming after those. The starting date is always the first of the month. I think I basically just need the macro to loop after the first stock or date range is completed.

Hello All,
I really appreciate all the help in this forum. Saves me a bunch of time and i have been able to learn a lot.

I need some help, I am working on the same thing Kaygu was doing but my data has some duplicate dates. I used the macro Alf put together and it works great, but I think my duplicate dates prompt an error. Can I get some help?

Thanks
Timothy

Hi Timothy

Welcome to this forum nice to have you here. One problem with you request for help is that you posted it in an existing thread and this is a no no as forum rule 4 states that.

Do not post a new help request in an existing thread

So you need to start you own thread. You may of course add a link to this thread if you think it would be useful. There is also the advantage when starting a new thread that you have a much better chance of getting help.

So add a descriptive title for you problem and upload a file as well showing before and after results and I'm certain somebody will help you.

Alf

Hey Alf,
Thanks for the info. I will do that.

Good to hear, I'll do a search using your forum name to find you new thread and see if I may have a possible suggestion on how to solve your problem.

Alf