 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / May 2008NUMBERS RUN IN ONE CELL
Is there anyway by which i can make one cell to continously show numbers running from 0 to 100,000 and then again start from 0. I have sheet where I refresh the external data and by the time data is refreshed I want one of the cells to show the pattern of running numbers. Sub CountDown() Static cnt As Long Range("A1").Value = cnt cnt = cnt + 1 If cnt > 100000 Then cnt = 0 End If Application.OnTime Now + TimeSerial(0, 0, 1), "CountDown" End Sub  Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Is there anyway by which i can make one cell to continously show numbers > running from 0 to 100,000 and then again start from 0. > > I have sheet where I refresh the external data and by the time data is > refreshed I want one of the cells to show the pattern of running numbers. Just as a point of interest, these four lines from your code.... cnt = cnt + 1 If cnt > 100000 Then cnt = 0 End If can be replaced by this single line... cnt = (cnt + 1) Mod 100001 Although I would note your code is marginally quicker than what I posted. Rick > Sub CountDown() > Static cnt As Long [quoted text clipped - 15 lines] >> I have sheet where I refresh the external data and by the time data is >> refreshed I want one of the cells to show the pattern of running numbers. But why? Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Just as a point of interest, these four lines from your code.... > [quoted text clipped - 31 lines] >>> refreshed I want one of the cells to show the pattern of running >>> numbers. But why what?... Why did I post my formula? I thought readers of this thread (whether in this newsgroup or in the Google archives) would be interested in seeing the equivalence between the longer (direct) method that you posted and the shorter (Mod function call) method that I posted. Why is your code marginally quicker? It takes longer to execute a Mod function than an If-Then test. Some other why? Rick > But why? > [quoted text clipped - 34 lines] >>>> refreshed I want one of the cells to show the pattern of running >>>> numbers. Why would you replace code that is very clear, very obvious in its intent, with code that is not, and offers no perceptible benefits. In other words, why use 'clever' code just for its own sake. Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > But why what?... > [quoted text clipped - 49 lines] >>>>> refreshed I want one of the cells to show the pattern of running >>>>> numbers. Personally, I don't consider this statement... cnt = (cnt + 1) Mod 100001 to be unclear, unobvious nor 'clever' in the sense I think you mean it. I find the above line to be every bit as understandable as the multi-line code you posted; but, of course, that is probably because I am intimately familiar with the Mod operator and how it works. The Mod operator (like its worksheet formula counterpart) can be a powerful programming tool when used with cyclical counting type processes (such as in this thread's subject matter or with calendar and time calculations) and I think it is one programmers should be introduced to if they are unfamiliar with it. My hope is that by my posting the equivalency of the above statement to your posted code, it might spark some programmers out there who experience a "Wow!" moment over it to take the time to learn more about this operator; those for who do not experience such a moment, well, nothing is really lost as they will simply read over it and move on. Rick > Why would you replace code that is very clear, very obvious in its intent, > with code that is not, and offers no perceptible benefits. [quoted text clipped - 55 lines] >>>>>> refreshed I want one of the cells to show the pattern of running >>>>>> numbers. thanks for your help Rick.. I am new to VB.. plz help me , what i have done is right click the sheet and pasted yoyr given code in the sheet . Once i run the code it is changing 0 to 1 and +1 onwards each time i run the code but in addition message is coming this macro could not be found. And one more thing whether it is possible or not , is there any way that i get by running macro only once numbers from 0 -100000. Thanks for your help in advance... > Just as a point of interest, these four lines from your code.... > [quoted text clipped - 30 lines] > >> I have sheet where I refresh the external data and by the time data is > >> refreshed I want one of the cells to show the pattern of running numbers. thanks for your help Bob.. I am new to VB.. plz help me , what i have done is right click the sheet and pasted yoyr given code in the sheet . Once i run the code it is changing 0 to 1 and +1 onwards each time i run the code but in addition message is coming this macro could not be found. And one more thing whether it is possible or not , is there any way that i get by running macro only once numbers from 0 -100000. Thanks for your help in advance... > Sub CountDown() > Static cnt As Long [quoted text clipped - 15 lines] > > I have sheet where I refresh the external data and by the time data is > > refreshed I want one of the cells to show the pattern of running numbers. It should be in a standard code module, not in the sheet module. Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > thanks for your help Bob.. > [quoted text clipped - 32 lines] >> > refreshed I want one of the cells to show the pattern of running >> > numbers. Thanks Bob... Your advice has been really very helpful... > It should be in a standard code module, not in the sheet module. > [quoted text clipped - 34 lines] > >> > refreshed I want one of the cells to show the pattern of running > >> > numbers. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.