Sequence of dates is not the desired in the macro

Question

I tried so many times to code with my own understanding, but the VBA code did not return correct outputs as I do manually.

The objective: Return the 2 below outputs:

a beginning date and an end date
and a 'cost check' in text.

Specific problem

+) The beginning date must run from 01/10/2025 (my computer using dd/mm/yyyy format) to 01/11/2026 for each step of a month => 01/10/2025, 01/11/2025, 01/12/2025, ..., until 01/11/2026

+) The end date is given (01/01/2032) and can be adjusted by shifting each step quarterly for a maximum of 8 quarters (2 years) => 01/01/2032, 01/10/2031, 01/07/2031, ..., until 01/01/2030.

+) For each pair of beginning date and end date, a cost associated will be calculated and the cost check will return "Yes" if the cost <= a given threshold, or "No" if the cost > the given threshold. The cost calculation and cost check are calculated on a sheet. We only care about the "Cost check" output whether it is "Yes" or "No" based on beginning and end dates.

+) I need to start with the end date (01/01/2032) and then start checking step by step the beginning dates from 01/10/2025 to 01/11/2026. If whenever the cost check returns "Yes", stop everything and return the 2 outputs above. If for all beginning date tried, the cost check still returns "No", then I need to move backward the end date 3 months to 01/10/2031. After that, do again checking step by step the beginning dates from 01/10/2025 to 01/11/2026. If whenever the cost check returns "Yes", stop everything and return the 2 outputs above, if not, i.e. the cost check still returns "No" with the date "01/11/2026", then continue to move backward the end date.

Sub CheckdateCost()

begin_date = WorksheetFunction.Date (2025,10,1)
end_date = WorksheetFunction.Date (2032,1,1)
nb_quarters_backward_min = 0
Do While Worksheets("Sheet1").Range("Check_Cost").Value = "No" And nb_quarters_backward_min <= 8
   nb_month_forward_min = 0
   Do While Worksheets("Sheet1").Range("Check_Cost").Value = "No" And nb_month_forward_min <= 12
      begin_date = WorksheetFunction.EDate(begin_date, nb_month_forward_min)
      Worksheets("Sheet1").Range("Begin_Date_OK").Value = begin_date
      nb_month_forward_min = nb_month_forward_min + 1
   Loop     
   nb_quarters_backward_min = nb_quarters_backward_min + 1
   end_date = WorksheetFunction.EDate(end_date, -3 * nb_quarters_backward_min)
   Worksheets("Sheet1").Range("End_Date_OK").Value = end_date
Loop
Worksheets("Sheet1").Range("Begin_Date_OK").Value = begin_date
Worksheets("Sheet1").Range("End_Date_OK").Value = end_date

End Sub

You say but the VBA code did not return correct outputs as I do manually. How exactly? What do you expect, and what does it do instead? — chris neilsen
– chris neilsen, Commented Aug 10 at 19:53
There are several problems in the code, so when the question is reopened I can show them if you will need at all.. Some: 1. There is no Date function in the WorksheetFunction object. 2. Not reset the inner While variable nb_month_forward_min. 3. Stepping should be constant 1 and -3 for respective Edate functions. — Black cat
– Black cat, Commented Aug 11 at 4:29
It would be good to rephrase the title to some descriptive and specific content. Like: Sequence of dates is not the desired in the macro when try to find an acceptable result with changing dates. — Black cat
– Black cat, Commented Aug 11 at 4:38

CDP1802 · Accepted Answer · 2025-08-11 19:52:10Z

0

Option Explicit

Sub CheckdateCost()

    Dim m As Long, q As Long, bYes As Boolean
    
    With Worksheets("Sheet1")
        For q = 0 To -8 Step -1
            ' end date
            .Range("End_Date_OK").Value = DateAdd("q", q, DateSerial(2032, 1, 1))
            For m = 0 To 13
               ' start date
                .Range("Begin_Date_OK").Value = DateAdd("m", m, DateSerial(2025, 10, 1))
               ' calc
               If .Range("Check_Cost").Value = "Yes" Then
                    bYes = True
                    Exit For
               End If
            
            Next
            If bYes Then Exit For
        Next
    End With
    
    If bYes Then
       MsgBox "Solved"
    Else
       MsgBox "Not solved"
    End If

End Sub

answered Aug 11 at 19:52

CDP1802

17.3k2 gold badges10 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

halfer Aug 25 at 16:22

Hi CDP. Would you add some explanatory text to this? Future readers will be able to understand how you solved the problem if you do.

Collectives™ on Stack Overflow

Sequence of dates is not the desired in the macro

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related