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

Lewzerrrr

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

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Trevor_S

Well-known Member
Joined
Oct 17, 2015
Messages
614
Office Version
  1. 2016
Platform
  1. Windows
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:

Lewzerrrr

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

Well-known Member
Joined
Oct 17, 2015
Messages
614
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,473
Messages
5,601,863
Members
414,479
Latest member
Beau the dog

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
Top