Check for every Friday of the week.

[donlincolnmre2]

Hello

I’m looking to modify this macro which shows the date on upcoming Friday so in this case it will be 04/01/22

What I’m looking for is to place this Friday date in Cell A1 and then the macro will check every day for that day, so e.g if today is Tuesday and Friday is in future then the macro will just abort so same thing with wed and Thursday and If Friday is = to Friday then macro should also abort.

Once its Sat then the date in cell A1 is for Friday so the macro should put the new upcoming Friday date and keep checking as above for each day.

Below is the code.

Any help would be greatly appreciated.

Thanks.

Sub VBA_Find_Next_Friday_Method1()

Dim dNext_Friday As Date

dNext_Friday = DateAdd("d", -Weekday(Now) + 13, Now)

MsgBox "If today's date is '" & Format(Now, "DD MMM YYYY") & "' then" & vbCrLf & _
" Next Friday Date is : " & Format(dNext_Friday, "DD MMM YYYY"), vbInformation, "Next Friday Date"

End Sub

 

[Fluff]

The excel version that i have is Excel 2000.

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

The formula I suggested won't work with your version of Excel.

 

[BSALV]

 

[BSALV]

weekday is also too recent for an excel2000 !
I'll come later today with another solution, first dinner.

 

[Fluff]

Weekday should work fine, especially as you are using the VBA version rather than the Excel function.

 

[BSALV]

just for the fun

Map2
	A
1	vr 01/04/22
Blad1

Cell Formulas
Range	Formula
A1	A1	=+TODAY()+(MOD(TODAY(),7)<>6)*(7-MOD(TODAY()+1,7))