automatic counting

[neilb]

I have a cell which has a number entered into it. I want to be able to use a
different cell to count upto that cell at the press of a key command... is
this possible?

example:


cell A1 = 12
Cell A2 should read cell A1 and count from 1 to 12 after a key command

 

[Nigel]

Add a form control button and assign the macro 'Count' to it.

Then add this code into a standard module, the line Sleep 1000 delays the
update by 1000 milli seconds, change this as required...

Option Explicit
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub Count()
Dim iC As Long
With ActiveSheet
For iC = 1 To .Range("A1")
Sleep 1000
.Range("A2") = iC
Next
End With
End Sub

 

[Bob Phillips]

Put this is a standard code module

Public nTick As Long

Public Sub CountDown()

With Range("A2")

.Value = .Value + 1
If .Value < nTick Then

Application.OnTime Now() + TimeSerial(0, 0, 1), "CountDown"
End If
End With

End Sub


Then add this to the worksheet code module

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target

If IsNumeric(.Value) Then

nTick = .Value
.Offset(1, 0).Value = 0
Call CountDown
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob

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