Insert week number

nparsons75

Well-known Member
Joined
Sep 23, 2013
Messages
1,227
Office Version
  1. 2016
I need to insert the current week number into a cell when I press a macro button (unless there is another way). I need to also enter a number into the cell as well as enter it automatically. So I will have the choice, press the button to enter the current week number or manually type the number in.
 

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.

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,301
Office Version
  1. 2013
Platform
  1. Windows
nparsons75,

Perhaps asign something like this to your button....
Code:
Sub Current_WN()
Range("A2") = Evaluate("WEEKNUM(TODAY(),)")
End Sub

Hope that helps.
 

nparsons75

Well-known Member
Joined
Sep 23, 2013
Messages
1,227
Office Version
  1. 2016
Hi, sorry to be a pain, how do I assign code to a button. (the button is just a shape I created) Do I need to insert a button? Apologies for sounding a bit naff, I have created buttons in VB but never into the spreadsheet, i guess very straight forward.
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,301
Office Version
  1. 2013
Platform
  1. Windows
Open the vb editor e.g. Alt F11 or right click sheet tab and View Code.

In the left hand project pane, double click on your desired sheet and paste the code into the white space code pane on the right.

Then in the worksheet, right click on your shape and select Assign Macro.
Select the Current_WN macro from the list and click OK
 

nparsons75

Well-known Member
Joined
Sep 23, 2013
Messages
1,227
Office Version
  1. 2016

ADVERTISEMENT

Hi I managed to create the button but get a compile error when running.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,197
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
nparsons75,

Perhaps asign something like this to your button....
Rich (BB code):
Sub Current_WN()
Range("A2") = Evaluate("WEEKNUM(TODAY(),)")
End Sub

Hope that helps.

Just pointing out that you can use native VBA functions to do the line I highlighted in red instead of having the Evaluate function reach out to the worksheet WEEKNUM and TODAY functions to do it. This line of code will work the same as the red highlighted one...

Range("A2") = DatePart("ww", Date)
 

nparsons75

Well-known Member
Joined
Sep 23, 2013
Messages
1,227
Office Version
  1. 2016

ADVERTISEMENT

Absoloutly fantastic guys, thanks to both of you.

I used this code:

Sub Current_WN()
Range("F5") = DatePart("ww", Date)
End Sub

Works a treat, thank you.
 

nparsons75

Well-known Member
Joined
Sep 23, 2013
Messages
1,227
Office Version
  1. 2016
Hi, for some strange reason this code has stopped working, yesterday worked perfectly but now it will not change the week number in the field, it flashed as if it did something but the field did not change.
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,301
Office Version
  1. 2013
Platform
  1. Windows
If you are running Excel 2007 or newer, did you save the file as MacroEnabled?
If you right click the button - Assign Macro can you still see the Macro in the list?
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,197
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Hi, for some strange reason this code has stopped working, yesterday worked perfectly but now it will not change the week number in the field, it flashed as if it did something but the field did not change.

Here in the US, yesterday was Tuesday and today is Wednesday... they are both in the same week so the week number should not change.
 

Forum statistics

Threads
1,137,301
Messages
5,680,705
Members
419,929
Latest member
Atlas Quinn

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