Find last sundays of the month in macros

Learning Hub Academy Feedback
, , ,
1

i need to find the last sunday of the month in macros

2

Try this. This is based on current date. Change it according to your need.

Sub LastSundayOfCurrentMonth()
’
’ LastSundayOfCurrentMonth Macro
’
Dim today As Date: today = Date
Dim day As Date: day = DateAdd(“d”, -1, DateAdd(“m”, 1, DateValue(Month(today) & " 1, " & Year(today))))
MsgBox (day)

While Weekday(day) <> 7
day = DateAdd(“d”, -1, day)
Wend
MsgBox (DateAdd(“d”, 1, day))

’
End Sub

4 Likes
3

image
image1306Ă—297 7.89 KB

i have given the today = “current date”
it hits error in day: =dateadd… as iam new to macros ,sorry for begineer queries

4

This link may help you, @priyankavivek

2 Likes
5

iam unable to open the link can you please provide screenshot

6

image
image323Ă—524 3.38 KB
i have series number of dates,where i should filter upto the last sunday of the month or week,can i get any ideas on it

7

Check this codes,
Code 1:
Sub VBA_Find_previous_Sunday_Method1()

Dim dPrevious_Sunday As Date

dPrevious_Sunday = DateAdd("d", -Weekday(Now) + 1, Now)

MsgBox "If today's date is '" & Format(Now, "DD MMM YYYY") & "' then" & vbCrLf & _
" Previous Sunday Date is : " & Format(dPrevious_Sunday, "DD MMM YYYY"), vbInformation, "Previous Sunday Date"

End Sub

Code 2:
'Previous Sunday Date using Excel VBA Functions
Sub VBA_Find_previous_Sunday_Method2()

 Dim dPrevious_Sunday As Date
 
dPrevious_Sunday = DateAdd("ww", -1, Now - (Weekday(Now, vbSunday) - 8))
Debug.Print dPrevious_Sunday
'Or
dPrevious_Sunday = DateAdd("ww", -1, Now - (Weekday(Now, vbMonday) - 7))
Debug.Print dPrevious_Sunday
'Or
dPrevious_Sunday = DateAdd("ww", -1, Now - (Weekday(Now, vbTuesday) - 6))
Debug.Print dPrevious_Sunday
'Or
dPrevious_Sunday = DateAdd("ww", -1, Now - (Weekday(Now, vbWednesday) - 12))
Debug.Print dPrevious_Sunday
'Or
dPrevious_Sunday = DateAdd("ww", -1, Now - (Weekday(Now, vbThursday) - 11))
Debug.Print dPrevious_Sunday
'Or
dPrevious_Sunday = DateAdd("ww", -1, Now - (Weekday(Now, vbFriday) - 10))
Debug.Print dPrevious_Sunday
'Or
dPrevious_Sunday = DateAdd("ww", -1, Now - (Weekday(Now, vbSaturday) - 9))
Debug.Print dPrevious_Sunday

MsgBox "If today's date is 12/03/2018 " & vbCrLf & " then previous Sunday's date is " _
& Format(dPrevious_Sunday, "DD MMMM YYYY"), vbInformation, "Previous_Sunday_Date"

End Sub

These are the codes which I found in that website.

2 Likes
8

No worries. Keep trying and you will rock :+1:

1 Like
9

thankyou

10

you declared today as Date and trying to assign some other value. You should assign as
today = DateValue(3,12,2020) or
today = DateValue(“Mar”,12,2020)

11

Run macro to reach each Date cell value, split the value to get date month and year through which you can create a date as Date(month, date, year) in the code provided (where current date is referred). At the end instead of MsgBox, write it into the cell.