CODEWITHSUNDEEP
excelvba
RSmith
RSmith

Reputation: 3

Adding last day of prior month to column in Excel

I'm trying to make all cells in column C to be the last day of the prior month. Since it's June, I'd like all cells in column C to be May 31 in this case. Additionally, I have data in column D and I'd only like May 31 2019 to populate in column C if there is data in column D.

I can't seem to 'hinge' column C against column D, thus I've left that part of the code out below.

Sub LastDayofPriorMonth()

    ActiveCell.FormulaR1C1 = "=DATE(2019, 5, 31)"

    Range("C3").Select

    Selection.AutoFill Destination:=Range("C3:C75")

    Range("C3:C75").Select

End Sub

Upvotes: 0

Views: 66

Answers (2)

Tom
Tom

Reputation: 9878

You can use EOMONTH

=EOMONTH(D3,-1)

or to add using VBA

Sub LastDayofPriorMonth()

    ActiveSheet.Range("C3:C75").Formula = "=EOMONTH(D3, -1)"

End Sub

This could be combined with @donPablo's comment to give you

Sub LastDayofPriorMonth()

    ActiveSheet.Range("C3:C75").Formula = "=IF(ISBLANK(D3),"""",EOMONTH(D3, -1))"

End Sub

Upvotes: 2

Alvaro CC
Alvaro CC

Reputation: 142

you can always get last day of prior month using the code below

Lastday=DateAdd("d", -Day(Date), Date)

You can use several ways to filter your data p.e

for r=3 to 72

if activesheet.cells(r,4)="" then activesheet.cells(r,3)=""

next

Let me know of any questions

Upvotes: 0

Related Questions