Excel VBA Function to Do Loop And Test Criteria

Background information: I am trying to test the future dates to check if they fall on Monday, Wednesday or Saturday. If the dates falls on one of the three criteria, the date is returned into the cell. I managed to get this part down.

Problem: I would like the function to automatically loop for another 45 days instead of only referencing from the first date.

Questions:

• Is there a more efficient way or neater way to write the code?


• Is it possible to perform loop in functions? Is yes, could you show and explain


• What are other possible methods? I am looking at expanding my knowledge, please share :)

Thank you for your time!

Public Function AFPSECONDOFFDAY(NextOffDay As Date) As String
Dim ans As String, dayCount As Integer, n As Integer

n = 2

' This statement returns the date into the cell if it is Monday, Wednesday or Saturday

If Format(NextOffDay + 2, "w", vbMonday) = 1 _
Or Format(NextOffDay + 2, "w", vbMonday) = 3 _
Or Format(NextOffDay + 2, "w", vbMonday) = 6 Then
' would like it to continue for another 45 days instead 
' of stopping after + 2 days
    AFPSECONDOFFDAY = NextOffDay + 2

Else
    'If the date does not meet the criteria, I would like it to _
    skip and do the next date. So that cells will be filled simultaneously.
    AFPSECONDOFFDAY = ""

End If

End Function