VBA - adding dates and rounding values

[dmj120]

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?

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

 

[Joe4]

Ok...so the in a round about way I'm confusing the code because I'm referring to a reference and a value simultaneously - kinda sorta?

Not quite.
It is a range variable, because you declared it here:

Dim myDateTimeRange As Range

So, it is set to range "V5", then the variable is actually the range V5, not the value in cell V5.
To get the value, you would tpyically then use:

Dim v as Double
v= myDateTimeRange.Value

What can make it really confusing is that sometimes people leave off the ".Value", and it is assumed to be here.

Range variables are set usign the "Set" command, i.e.

Set myUpdatedRange = Range("W" & Target.Row)

Value variable do NOT use "Set", i.e.

Dim v as Double
v= myDateTimeRange.Value

The long and short of it is: Is column C is NOT "done" nothing should happen.

Then the easiest thing to do then is to exit the procedure without doing anything else at that point, i.e.

    If Range("C" & Target.Row) = "done" Then
        Set myDateTimeRange = Range("V" & Target.Row)
    Else
        Exit Sub
    End If

 

[dmj120]

Then the easiest thing to do then is to exit the procedure without doing anything else at that point, i.e.

    If Range("C" & Target.Row) = "done" Then
        Set myDateTimeRange = Range("V" & Target.Row)
    Else
        Exit Sub
    End If

Thanks for the explanation. I'll try this else exit sub.

 

[dmj120]

I appreciate all your help. This exit prevents the rest of the code (ie check 2).

I'm going to spend more time reviewing all the above posts and try to work more on getting this to work.

i'm starting to think I'm getting closer with this

Private Sub Worksheet_Change(ByVal Target As Range)
'Timestamp Data -- TeachExcel.com
'**** REVISED/FIXED by Joe4 @ MrExcel forum ****

'***CHECK 1*****

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

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

'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
    If Range("C" & Target.Row) = "done" Then
        Set myDateTimeRange = Range("V" & Target.Row)
    Else
        Exit Sub
    End If


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

    'Determine if the input date/time should change
    If myDateTimeRange Is Nothing 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:K55000"))
   
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

 

[Joe4]

I appreciate all your help. This exit prevents the rest of the code (ie check 2).

I thought that is what you wanted to happen, based on your previous response.

If it is not, get rid of those lines we just added, and add everything that you ONLY want to happen when column C is done inside of that IF...THEN block, under the

        Set myDateTimeRange = Range("V" & Target.Row)

line of code.

One of the most important things to do before writing code is to sketch out how you want off this to work.
What should happen when certain conditions are met, etc.
Once you have all that sketched out, then you can use that as your guide when writing your code.