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

Lewzerrrr

Active Member
Joined
Jan 18, 2017
Messages
256
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

Code:
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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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.
 
Last edited:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top