Last day of week problem

[DaveUK]

Please can someone suggest the VBA code to do the following.

The last day of the week is Sunday.
The format i want for the date is : dd/mm/yyyy

I want to store the data of the last day of the week in a variable LastDayOfWeek. (pre Dimmed as Dim LastDayOfWeek as Date)

I want then to check for the value of LastDayOfWeek in row3 starting at column B and then across to the right. If the value is found i then want to exit the sub otherwise i want the value LastDayOfWeek pasted into the next Empty Cell in Row 3.

TIA

 

[scifibum]

If I understood you correctly, then this should work:

Sub LaLa()
Dim LastDayOfWeek As Date, r As Long, c As Integer

LastDayOfWeek = Int(Now())
Do Until Weekday(LastDayOfWeek) = vbSunday
LastDayOfWeek = LastDayOfWeek + 1
Loop
r = 3
c = 2

Do While Cells(r, c).Value <> ""
If Cells(r, c).Value = LastDayOfWeek Then Exit Sub
c = c + 1
Loop
Cells(r, c) = LastDayOfWeek

End Sub

 

[DaveUK]

Thats exactly what i wanted !!!

Many thanks

 

[DaveUK]

Resurrecting an old topic but how do i alter the code to give me last weeks end of the week day.

For example today is 15th March if i wanted to find out last Sundays date.

I will be running the macro on a weekday and need the end of week date of last Sunday.(The week just gone).

TIA

 

[scifibum]

Just change

LastDayOfWeek = LastDayOfWeek + 1

to

LastDayOfWeek = LastDayOfWeek - 1

You'll then be going backwards 1 day at a time until the weekday is Sunday

 

[ringo]

is there a way to do this to multiple rows (i.e F4 -F17) ?