VBA code to Reset counter in a cell to 1, everyday (newbie here)

[radian89]

Hi all,

I have little experiments here, I want to know the code to reset the counter based on changes of day. so when ever i click Add number button, will add the counter +1, become 1,2,3 and so on, but it will reset to 1 again after the next day.

how to do it?

[/IMG]

thanks a lot for the help

 

[Michael M]

maybe something like

Sub workbook_open()
If Range("C3").Value <> today() Then
Range("C3").Value =today() 
Range("C5").Value = 0
End If
End Sub

 

[radian89]

maybe something like

Sub workbook_open()
If Range("C3").Value <> today() Then
Range("C3").Value =today() 
Range("C5").Value = 0
End If
End Sub

Hi Michael,

Thanks for the help, but i got error with the function today()
got this prompt

[/IMG]

 

[Michael M]

oooow...I shouldn't have a beer after golf...

Sub workbook_open()
If Range("C3").Value <> Now() Then
Range("C3").Value = Now()
Range("C5").Value = 0
End If
End Sub

 

[Peter_SSs]

oooow...I shouldn't have a beer after golf...

Having a beer after golf is not a problem as far as I can see.
Posting in the forum after a beer after golf is more problematic.
.. especially twice.

I'm wondering how you could run this code and have this test ever evaluate to False?
That is, C5 will be set to zero whenever the workbook is opened.

Sub workbook_open()
If Range("C3").Value <> Now() Then
Range("C3").Value = Now()
Range("C5").Value = 0
End If
End Sub

[/QUOTE]

 

[radian89]

oooow...I shouldn't have a beer after golf...

Sub workbook_open()
If Range("C3").Value <> Now() Then
Range("C3").Value = Now()
Range("C5").Value = 0
End If
End Sub

Having a beer after golf is not a problem as far as I can see.
Posting in the forum after a beer after golf is more problematic.
.. especially twice.

I'm wondering how you could run this code and have this test ever evaluate to False?
That is, C5 will be set to zero whenever the workbook is opened.

Sub workbook_open()
If Range("C3").Value <> Now() Then
Range("C3").Value = Now()
Range("C5").Value = 0
End If
End Sub

[/QUOTE]

Hi Michael & Mrexcel MVP,

can't stop laughing.... sorry for late reply, after a bit of searching, i found this code

it's to make reset C5 to 1 whenever the file opened greater than 7 am, I can't figure out the logic, if i want to not reset the counter if it open from the same day, and only reset if it open tomorrow morning.

Private Sub Workbook_Open()

If Now() > TimeValue("07:00:00") Then
Sheets("Sheet1").Range("C5").Value = 1
End If




End Sub

thanks a lot for the help

 

[Peter_SSs]

I can't figure out the logic, if i want to not reset the counter if it open from the same day, and only reset if it open tomorrow morning.

Try

Sub Workbook_Open()
  If Time > TimeValue("07:00:00") And Date > Range("C3").Value Then
    Range("C3").Value = Date
    Range("C5").Value = 1
  End If
End Sub

.. or if the 7am really has nothing to do with it and you just want it reset if the day has changed then

If Date > Range("C3").Value Then

 

[radian89]

Try

Sub Workbook_Open()
  If Time > TimeValue("07:00:00") And Date > Range("C3").Value Then
    Range("C3").Value = Date
    Range("C5").Value = 1
  End If
End Sub

.. or if the 7am really has nothing to do with it and you just want it reset if the day has changed then

If Date > Range("C3").Value Then

Hi MrExcel MVP,

range "C3", contain formula, =now(), is the code above, will understand?

if Date > range("C3").value then ...

while range "C3" will value now which the above condition won't be meet. am i right?

thanks a lot for the help

 

[Peter_SSs]

Hi MrExcel MVP,

range "C3", contain formula, =now(), is the code above, will understand?

if Date > range("C3").value then ...

while range "C3" will value now which the above condition won't be meet. am i right?

thanks a lot for the help

I'm sorry, I don't understand that very well.
However, if you are using cell C3 to help you only reset the counter when the workbook is opened on another day, then it should not have a formula in it. All it needs to do is record the date when the counter was last reset.
Every time the workbook is opened it checks the current date against the date in C3. If they are the same then no action is taken. If they are different then the counter is reset and the new date is recorded in C3.

Does 7am actually have anything to do with when the counter should be reset, or is it reset only on a new day?

 

[radian89]

Does 7am actually have anything to do with when the counter should be reset, or is it reset only on a new day?

Hi Mr Excel MVP,

no it doesn't have anything to do with the counter, you can replace it with new code.
i just make it to get the nearest condition, which is reset only on new day

So, if there's no other option, C3, need not contain formula, and user define it manually, noted

thanks a lot.