Require Variable to Hold Scope over 24 hour period only

WednesdayC

Board Regular
Joined
Nov 7, 2010
Messages
186
Hi All

Can anyone help me please.

I have a Public count variable, whose value is in a cell on a Worksheet, which clicks up automatically on calling a certain macro. What I am trying to achieve is that even if the Workbook is closed down and reopened, provided the date has not clicked over to the next day, the Count variable will continue to click up, whenever the macro is called.

However whenever the date clicks over to the next day, the Count variable should reset to 0.

I am not sure how to achieve this.

Thanks in advance.

Wednesday
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Ruddles

Well-known Member
Joined
Aug 24, 2010
Messages
5,786
Store the date in an adjacent cell. When the macro runs, check the date in the adjacent cell: if it's the same as today's date, increment the counter; if it's different to todays's date, reset the counter to zero.
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,069
Office Version
  1. 2013
Platform
  1. Windows
WednesdayC,

Perhaps with your count in eg cell A2 and the date it was last re-set in B2.....
Excel 2007
A
B
1
Count
Date Set
2
45
13/04/2013

<TBODY>
</TBODY>


<TBODY>
</TBODY>



The following code on opening.

Code:
Private Sub Workbook_Open()
If Date > Sheets("Sheet1").Range("B2") Then
Sheets("Sheet1").Range("A2") = 0
Sheets("Sheet1").Range("B2") = Date
End If
'set yor variable
PublicCountVar = Sheets("Sheet1").Range("A2")
End Sub

Hope that helps
 
Last edited:

WednesdayC

Board Regular
Joined
Nov 7, 2010
Messages
186
Hi Ruddles

Thank you for your reply.
Of course, it seems so obvious now.
Just what I need.

Regards
Wednesday
 

WednesdayC

Board Regular
Joined
Nov 7, 2010
Messages
186
Hi Snakehips
Love the Name!

Thanks for your help. Both you and Ruddles have provided the answer I need.

Regards
Wednesday
 

Watch MrExcel Video

Forum statistics

Threads
1,122,186
Messages
5,594,745
Members
413,929
Latest member
Hypatia

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
Top