NUMBERS RUN IN ONE CELL

[SANDIND]

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.

 

[Bob Phillips]

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


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Bob Phillips]

But why?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[SANDIND]

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...

 

[SANDIND]

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...

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Bob Phillips]

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.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob Phillips]

It should be in a standard code module, not in the sheet module.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[SANDIND]

Thanks Bob...

Your advice has been really very helpful...

 

[Rick Rothstein \(MVP - VB\)]

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