VBA - adding dates and rounding values

dmj120

Active Member
Joined
Jan 5, 2010
Messages
308
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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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.
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

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.

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.
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.

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
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!!

I appreciate the explanation. Unfortunately, no it's still not working.
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,853
Members
452,361
Latest member
d3ad3y3

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