Number Generation

[DILANHJ]

Hi Guys,

Can anybody help me to write up a formula to generate a number which will display a number from 1 to 10 every 30 seconds ?
number should display in ascending order at a time, which will change to next after 30 seconds.

Regards
Dilan

[Glenn Kennedy]

This can only be done with VBA. If that is acceptable I will move your thread to the VBA sub-forum.

[DILANHJ]

Hi Glenn,

Im ok with it.

Thank you

Regards
Dilan

[Roel Jongman]

Hi Dilanhj..

try this code. I used 1 second for testing otherwise it takes you 5 minutes to know if it gets to 10 or not
change 1 in the timeserial to 30 to wait 30 secs

Please Login or Register  to view this content.

Also in your own document
- Change Activesheet to the sheetname f.e. worksheets("Sheet1") so the counter does not randomly popup on any sheet
- Change Range("A1") to the actual cell where you want to show the counter

[DILANHJ]

Hi Roel,

It worked. Can you make the result at "A1" loop continuously once the count reaches 10 and start allover from number 1 again

Regards
Dilan

[Rick Rothstein]

It worked. Can you make the result at "A1" loop continuously once the count reaches 10 and start allover from number 1 again

If you do that, you will lock Excel up in an endless loop and you will not be able to use it until you have Windows Task Manager kill the process. There should be ways to get around this problem by adding some additional event procedures, but you still will not be able to use Excel while the count is going on. Is that what you really want?

[DILANHJ]

Hi Rick,

Yes, I need a endless loop. the sheet will be used as a dashboard.

I did tried a formula with rand() function "=INT(RAND()*(10-1)+1)". The formula gets updates when the sheet is auto-updated or saved. The problem with the formula is, it returns a random number within the range given. my requirement is to return a incremental number from the range and loop.


Regards
Dilan

[saravnepali]

You can use APPLICATION.ONTIME function instead of Application.Wait

Please Login or Register  to view this content.

You can call the STOPPROCESS procedure when the user closes the work book

Please Login or Register  to view this content.

[DILANHJ]

Hi Saravnepali,

As im new VBA, i did put the code on sheet and executed with "run Sub" command. I got an an error message "Cannot run the macro "***". The macros may not be available in this workbook or all macros may be disabled". However I see the cell contents at "A1" has increased.



All trust settings are enabled