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

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,653
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
50,653
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,297
Messages
5,571,401
Members
412,387
Latest member
qxyy
Top