VBA - adding dates and rounding values

D

dmj120

Active Member
Joined
Jan 5, 2010
Messages
310
Office Version
  1. 365
  2. 2019
  3. 2010
I copied the code Joe helped with into a new workbook and updated the cell ranges accordingly. Everything worked fine until I entered a non integer (ie n/a and no bid). Now nothing happens - I broke it somehow. I got a 'debug' popup but closed it out too soon, and can't get it back.

How do I update this so if a value entered in cells H5:K11000 are not numbers the code will still work?

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
'Timestamp Data
'   TeachExcel.com

'***CHECK 1*****

Dim myTableRange As Range
Dim myDateTimeRange As Range
Dim myUpdatedRange As Range

'Your data table range
Set myTableRange = Range("H5:K11000")

'Check if the changed cell is in the data tabe or not.
If Not Intersect(Target, myTableRange) Is Nothing Then

    'Stop events from running
    Application.EnableEvents = False

    'Column for the date/time
    Set myDateTimeRange = Range("V" & Target.Row)
    'Column for last updated date/time
    Set myUpdatedRange = Range("W" & Target.Row)

    'Determine if the input date/time should change
    If myDateTimeRange.Value = "" Then
        myDateTimeRange.Value = Date
    End If

    'Update the updated date/time value
    myUpdatedRange.Value = Now

    'Turn events back on
    Application.EnableEvents = True

End If

'***CHECK 2***
Dim rng As Range
Dim cell As Range
   
Set rng = Intersect(Target, Range("H5:K7500"))
   
If Not rng Is Nothing Then
    Application.EnableEvents = True
   
    For Each cell In rng
        cell.Value = Round(cell.Value / 365, 2) * 365
    Next cell
   
    Application.EnableEvents = False
End If

End Sub
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
Try this:
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
'Timestamp Data
'   TeachExcel.com

'***CHECK 1*****

Dim myTableRange As Range
Dim myDateTimeRange As Range
Dim myUpdatedRange As Range

'Your data table range
Set myTableRange = Range("H5:K11000")

'Check if the changed cell is in the data tabe or not.
If Not Intersect(Target, myTableRange) Is Nothing Then

    'Stop events from running
    Application.EnableEvents = False

    'Column for the date/time
    Set myDateTimeRange = Range("V" & Target.Row)
    'Column for last updated date/time
    Set myUpdatedRange = Range("W" & Target.Row)

    'Determine if the input date/time should change
    If myDateTimeRange.Value = "" Then
        myDateTimeRange.Value = Date
    End If

    'Update the updated date/time value
    myUpdatedRange.Value = Now

    'Turn events back on
    Application.EnableEvents = True

End If

'***CHECK 2***
Dim rng As Range
Dim cell As Range
   
Set rng = Intersect(Target, Range("H5:K7500"))
   
If Not rng Is Nothing Then

    Application.EnableEvents = False
   
    For Each cell In rng
        If IsNumeric(cell.Value) Then
            cell.Value = Round(cell.Value / 365, 2) * 365
        End If
    Next cell
   
    Application.EnableEvents = True
    
End If

End Sub

Note: If you errored out in the middle of the code, you may need to re-enable events by manually running this code:
VBA Code: 
Sub ReEnableEvents()
    Application.EnableEvents = True
End Sub
 
Upvote 0
Solution
How do you run this manually? I tried copying it into a new module and another script, didn't seem to reset anything.
Joe4 said:
Note: If you errored out in the middle of the code, you may need to re-enable events by manually running this code:
VBA Code: 
Sub ReEnableEvents()
    Application.EnableEvents = True
End Sub
Click to expand...

1650474605909.png
 
Upvote 0
Just select "ReEnableEvents" and click on the "Run" button.
You won't see anything happen, but things should be reset, so the automated code will run again.
 
Upvote 0
Thanks Joe. It's still not working -tried running, saving, exiting, rinse-repeat, nothing-, so I think I'll try creating a duplicate and starting from scratch.

Joe4 said:
Just select "ReEnableEvents" and click on the "Run" button.
You won't see anything happen, but things should be reset, so the automated code will run again.
Click to expand...
 
Upvote 0
dmj120 said:
Thanks Joe. It's still not working -tried running, saving, exiting, rinse-repeat, nothing-, so I think I'll try creating a duplicate and starting from scratch.
Click to expand...
Out of curiosity, did you copy over the ENTIRE code I posted, or just edit your existing code (for the original procedure)?
It looks like that you had your "Application.EnableEvents" codes lines backwards in your second block (not sure why you reversed them from the original code I posted in the other thread).
So if you did not correct those, you will likely still have issues.
 
Upvote 0
First I just added the IF isnumeric, then I copied pasted the whole thing.

I tried changing the true/false to see if anything would change - after it broke, in an attempt to "see" if there was any difference. Rookie mistake, learning the hard way, haha.

Just double checked and the second block is as the original was plus the if statement.

Joe4 said:
Out of curiosity, did you copy over the ENTIRE code I posted, or just edit your existing code (for the original procedure)?
It looks like that you had your "Application.EnableEvents" codes lines backwards in your second block (not sure why you reversed them from the original code I posted in the other thread).
So if you did not correct those, you will likely still have issues.
Click to expand...
 
Upvote 0
So, is everything working now?

Just a note, to make sure that you have clear understanding what everything is/does.
This line:
VBA Code: 
Application.EnableEvents = False
disables ALL event procedure code (including our "Worksheet_Change" code).
This is typically done so the updates made in the code don't cause the code to call itself, and get caught in an infinite loop.

For example, let's say that we had code that everytime cell A1 is changed, we add 1 to it.
Well, the change of the code adding 1 to cell A1 is changing the value, so then the code recognizes that, and calls the change code again, etc, etc.
(I don't know if you have ever been caught in an endless loop, but it is a pain! You usually need to kill the whole Excel session to stop it).

So, in automated VBA code, you will often see that line just before lines of code that update certain cells.

Then, after all updates are made, you want to turn it back on with this line:
VBA Code: 
Application.EnableEvents = True
otherwise, all event procedure code will be disabled (i.e. none of your automated code will work!).

Sometimes, in the process of testing, your code will "bomb out" halfway through, maybe after it disabled events, but it never made it to the line that turns them back on.
So, none of your automated code will work.
That little procedure I sent you is just a way to turn them back on manually (closing out of Excel and re-opening it will also reset it, but that is not as convenient).
 
Upvote 0
I appreciate the explanation. Unfortunately, no it's still not working.
 
Upvote 0
Holy Cannoli..... I am such an idiot! I didn't have the range correct - needed MORE rows. ?

Everything is working correctly. Thanks again Joe!!

dmj120 said:
I appreciate the explanation. Unfortunately, no it's still not working.
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Question Question
VBA date stamp fails when values are copied over
Replies
2
Views
426
OlgaB
O
A
  • Question Question
VBA help...Excel keeps crashing
Replies
2
Views
64
Peter_SSs
Peter_SSs
J
  • Question Question
Copying Rows Based on Column Value
Replies
1
Views
180
jolivanes
jolivanes
S
  • Question Question
Using Defined name and/or direct formula in VBA
2
Replies
11
Views
482
Schturman
S
A
  • Solved
If a cell contains the word "Completed", then return the date/time when it changed.
2
Replies
14
Views
853
dreid1011
dreid1011

Forum statistics

Threads
1,224,821
Messages
6,181,163
Members
453,021
Latest member
Justyna P

Share this page

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