cant figure this one out

rpwagner

New Member
Joined
Feb 28, 2002
Messages
22
assume a1 is a counter. It counts nr of contracts sold for 1 minute, then flips back to 0 at the begining of the next minute and counts contracts again for the full minute etc etc.

assume b1 is a counter, which counts the seconds of the minute rolling back to 1 second at the beginning of the next minute.

I want to accumulate in a bucket, the number of contracts sold (from a1) for seconds 1 thru 15 and put them into a bucket c1.
Then, in d1, i want the contracts sold from 1 to 30 seconds. Then in e1 I want the contracts sold from 1 to 45 and finally in f1 I want the contracts (still getting them a1) for seconds 1 to 59.

And if that is not enough, I want to compare them with the number of contracts sold IN The Previous full minute.

My contracts keep going back to 0 whenever the seconds go to the next quarter minute.
hhheeeellllllppppp Thanks
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
On 2002-03-07 05:45, rpwagner wrote:
assume a1 is a counter. It counts nr of contracts sold for 1 minute, then flips back to 0 at the begining of the next minute and counts contracts again for the full minute etc etc.

assume b1 is a counter, which counts the seconds of the minute rolling back to 1 second at the beginning of the next minute.

I want to accumulate in a bucket, the number of contracts sold (from a1) for seconds 1 thru 15 and put them into a bucket c1.
Then, in d1, i want the contracts sold from 1 to 30 seconds. Then in e1 I want the contracts sold from 1 to 45 and finally in f1 I want the contracts (still getting them a1) for seconds 1 to 59.

And if that is not enough, I want to compare them with the number of contracts sold IN The Previous full minute.

My contracts keep going back to 0 whenever the seconds go to the next quarter minute.
hhheeeellllllppppp Thanks

I think this will do the trick. I named
A1 as CounterM; B1 as CounterS; C1 to F1
as Quarters; and G1 as Previous.

I also assumed that a cell named Contracts
would keep a running total of the number of
contracts sold. (You may have another way
of keeping track of the contracts, but the
following logic should still work).

'In the ThisWorkbook object:
Private Sub Workbook_Open()
Application.OnTime Now, "TimeLord"
End Sub

'In a regular module:
Public OldContracts As Long, NewContracts As Long

Sub timelord()
Dim WhatTime As Long

WhatTime = Second(Now)
With ActiveSheet
.Range("CounterS").Value = WhatTime
NewContracts = .Range("Contracts").Value - OldContracts
OldContracts = .Range("Contracts").Value
.Range("CounterM").Value = .Range("CounterM").Value + NewContracts
End With

With ActiveSheet.Range("Quarters")
.Cells(4).Value = .Cells(4).Value + NewContracts
If WhatTime <= 15 Then .Cells(1).Value = .Cells(1).Value + NewContracts
If WhatTime <= 30 Then .Cells(2).Value = .Cells(2).Value + NewContracts
If WhatTime <= 45 Then .Cells(3).Value = .Cells(3).Value + NewContracts
End With

With ActiveSheet
If WhatTime = 0 Then
.Range("Previous").Value = .Range("CounterM").Value
.Range("Quarters").ClearContents
.Range("CounterM").Value = 0
End If
End With

Application.OnTime Now + TimeValue("00:00:01"), "timelord"
End Sub

This seems to work. Good luck!
 
Upvote 0
Tim, this is a great web page and guys like you make it greater. I will put the code in and try it out. And no, I am not going to give you any stock tips... we can both lose enough money trading ourselves with out anyone's help (on trading) but thanks again for your efforts on the excel I am going to put them in and try em.
 
Upvote 0
Tim, Or anyone for that manner) just a quick question. whenyou say put it into ThisWorkbook module and the next routine in a regular module, could you briefly explain how to do that. I have used vba before in earlier versions of excel but not sure of how to place the routines you supplied in the workbook module and a regular module. Thanks in advance.

_________________
Never forget: "Time's running out, let's roll"
This message was edited by rpwagner on 2002-03-08 06:59
 
Upvote 0
On 2002-03-08 06:58, rpwagner wrote:
Tim, Or anyone for that manner) just a quick question. whenyou say put it into ThisWorkbook module and the next routine in a regular module, could you briefly explain how to do that. I have used vba before in earlier versions of excel but not sure of how to place the routines you supplied in the workbook module and a regular module. Thanks in advance.

_________________
Never forget: "Time's running out, let's roll"
This message was edited by rpwagner on 2002-03-08 06:59

The first sub goes into the ThisWorkbook object that's automagically listed in the
Project Explorer int he Visual Basic editor.

The second sub can go into any VBA module--
you may need to create one if you don't have
a VBA macro already in the file.

HTH.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top