Fill blank cells in column with consecutive end-of-month dates

fruela

New Member
Joined
Oct 23, 2020
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
Hi all, new to VBA and about to fully automate my first report, thank to videos, books and mrexcel.

Not sure how to approach this last one... so for column Q, fill blanks with consecutive end-of-month date, same range as column W.

Result (for cells below 31/10/2020):
30/11/2020
31/12/2020
31/01/2021
etc

Capture3.PNG


Found this thread, but dates are fixed, and in my case they may vary each report:

Cheers
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,084
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub fruela()
   With Range("Q2:Q" & Range("W" & Rows.Count).End(xlUp).Row)
      .SpecialCells(xlBlanks).FormulaR1C1 = "=eomonth(r[-1]c,1)"
      .Value = .Value
   End With
End Sub
 
Solution

fruela

New Member
Joined
Oct 23, 2020
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
Super perfect, thank you Fluff!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,084
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,178
Messages
5,576,557
Members
412,731
Latest member
yaseen381
Top