• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Recalculate Random numbers weekly ONLY

raggd

New Member
I am currently building a database of rooms that each have a four digit passcode that needs to change on a weekly basis. I think i can calculate random numbers for each 'room' but i am not sure how to make it calculate only once every week.

I have been trying to modify the below link code but as this only generates a random number for each new cell I am unsure how to give a new random number on every weekly calculation... a code to clear all cells in column then recalculate all possibly?
http://www.mrexcel.com/forum/excel-questions/396985-create-new-products-numbers-excel.html
 
You should be able to accomplish this by calling on Today() and Weekday().
So if you want it to run on every Monday, it will be something like.
Code:
IF Weekday(Today()) = vbMonday Then

Insert your code after.

However, to fully automate, you can just code VBA as Workbook.Open Event and use batch file and Windows Task Scheduler to schedule update (of course, you will have to store generated number in another field so that it won't over write when you open it manually).
 
Thank you Chihiro.

I ended up using a UDF to get a random number:
Code:
Public Function Rand_once(ByVal r As Range)
    Rand_once = Rnd
End Function
then inserting into:
=ROUND(rand_once(A2)*10000,0)
with formatting "0000"
This doesn't give unique numbers but close enough for what I need, e.g. what's the password.
Then whenever i need to recalculate I have a copy and paste the same VBA code to recalculate. I will now try to combine with your weekday function to get a weekly calculation.

Question: as
Code:
IF Weekday(Today()) = vbMonday Then
is true for 24 hours, then won't it recalculate many times over on monday or will it only do it once?

Many thanks for your help so far,
 
It will recalculate, whenever you call upon the code on certain day of the week.
I'd recommend to stop the code when value is already present in output cell.
 
Back
Top