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