Incrementing year by 1 if week number is 27 but only triggering once a year VBA

[Lewzerrrr]

Hi,

I just can't get me head around what is probably an easy solution.
The code below is a date inputter into a sheet that on workbook open but only triggers if it's a new day. It inputs the current season, year, week number and day.
What I need is for when the week number gets to 27, the year is incremented by 1. As it currently stands, when it gets to 27 it WILL increment by 1 but then when it's a new day, it will increment again which I do not want to happen, in theory it should only increment every year.

How can I work around this?

Thanks

Private Sub Workbook_Open()

CurrentSeason = Evaluate("=IF(" & [WeekCell] & "<27,""AW"",""SS"")") '' AW = Weeks 1 - 26, SS = Weeks 27 - 52/53
CurrentWeek = Evaluate("=WEEKNUM(TODAY()-246,1)") '' Company week 1 is offset from global calendar week 1
CurrentDay = Evaluate("=TEXT(TODAY()-1, ""dddd"")") '' Reporting day


    If CurrentDay <> [DayCell] Then '' Allows code to only run when it's a new day
        
        If MsgBox("Do you want to update the date?", vbYesNo) = vbYes Then
        
            [SeasonCell] = CurrentSeason
            '[WeekCell] = CurrentWeek
            If [WeekCell] = 27 Then '' This logic needs changing
                CurrentYear = [YearCell] + 1
            Else
                CurrentYear = [YearCell]
            End If
            [YearCell] = CurrentYear
            '[DayCell] = CurrentDay
        
        End If
        
    End If
    
End Sub

 

[Trevor_S]

If you have a spare cell, then when the macro increments the year, get it to write the year into that cell. Then right at the start of the macro, use an IF statement to check whether the current year matches the year written to that cell. Only run the rest of the macro if it differs.

I.e. at first, the cell will be blank. When we get to week 27 this year, your macro will increment the years by one and write 2019 into the spare cell. After that, each time you open the spreadsheet, the macro will stop at that first IF statement, until January 2020 when it will start to run through, but only increment again in week 27 of that year.

 

[Lewzerrrr]

If you have a spare cell, then when the macro increments the year, get it to write the year into that cell. Then right at the start of the macro, use an IF statement to check whether the current year matches the year written to that cell. Only run the rest of the macro if it differs.

I.e. at first, the cell will be blank. When we get to week 27 this year, your macro will increment the years by one and write 2019 into the spare cell. After that, each time you open the spreadsheet, the macro will stop at that first IF statement, until January 2020 when it will start to run through, but only increment again in week 27 of that year.

Yes this is similar to what I eventually done last night, used a if week is week 27 and helper is non blank then input value into helper cell and increment year by 1. After this code is if week is week 28 then clear helper cell.

If there is a way to do this without helper cell that would be amazing to see

Thank you Trevor

 

[Trevor_S]

Not sure how you would do it otherwise, you need some form of "marker" to indicate that it had already been done for the year. You can't put it in variants, as they would lose their values, so would have to be on that spreadsheet, or some other file which the spreadsheet could refer to.