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]

Holy Cannoli..... I am such an idiot! I didn't have the range correct - needed MORE rows. ?

Everything is working correctly. Thanks again Joe!!

You are welcome.

Note: We can make the ending row number dynamic.
If you can tell us what column we can look at to determine what the last row with data is, then we can dynamically build that into the code.

For example, if we know that for every row of data, column K has to be populated, we can change this:

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

to this:

'***CHECK 2***
Dim lr As Long
Dim rng As Range
Dim cell As Range
  
'Find last row with data in column K
lr = Cells(Rows.Count, "K").End(xlUp).Row

Set rng = Intersect(Target, Range("H5:K" & lr))

(This just shows the example to the second block, but it can easily be applied to both blocks - just do the last row calculation at the top of the code and apply it to both ranges).

 

[dmj120]

that's awesome! Column C will always be populated. I will try updating the code with this dynamic row count.

 

[dmj120]

[sigh] learned the value of the vba pause button, haha. AND dragging down values while the dataset is filtered causes all sorts of problems!!! In that the 'hidden' rows are also populated....learned that the hard way ?

This project keeps growing so I created a small 'testing' file so when I break things, hours of work isn't wasted...again.

I have a formula in Col C =if(and(H:K<>""),"done",""). I added this column because cols H:K now no longer suffice........ so I've attempted to start modifying the code to start thinking/visualizing differently. I didn't try the above dynamic-row piece suggestion because I didn't want to break things ---- although I probably should since I'm at 52.4k rows.

Is it possible to add an IF statement to the Set myDateTimeRange = Range("V" & Target.Row) so that the date in col V is entered only when Col C is "done?" Or does this change require more changes (with check 1)?

I think adding another condition requires the two date functions be separated into two Dim's.

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

'***CHECK 1*****

Dim myTableRangeDone As Range
Dim myTableRangeUpdate As Range
Dim myDateTimeRange As Range
Dim myUpdatedRange As Range

'Your data table range
Set myTableRangeDone = Range("C5:C55000")  'Col C indicates row is 'done'/add date in V
Set myTableRangeUpdate = Range("H5:K55000")  'range of 4 price points - periodically updated

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

    'Stop events from running
    Application.EnableEvents = False

    'Column for the date/time - is row 'done' (completely priced), poss. need an IF
    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: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]

Is it possible to add an IF statement to the Set myDateTimeRange = Range("V" & Target.Row) so that the date in col V is entered only when Col C is "done?" Or does this change require more changes (with check 1)?

You mean like this?

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

 

[dmj120]

You mean like this?

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

Yes, thank you. I will try to implement this.

 

[dmj120]

I have lost my mind with with this ? What object variable am I missing??

If I just add this, I get an object error.

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

Trying to add the two other dims that I first assumed, and then tried several "tweaks" ......ugh.

'***CHECK 1*****

Dim myTableRangeDone As Range
Dim myTableRangeUpdate As Range
Dim myDateTimeRange As Range
Dim myUpdatedRange As Range

'Your data table range
Set myTableRangeDone = Range("C5:C55000")  'Col C indicates row is 'done'/add date in V
Set myTableRangeUpdate = Range("H5:K55000")  'range of 4 price points - periodically updated

'Check if the changed cell is in the data tabe or not.
If Not Intersect(Target, myTableRangeUpdate) Is Nothing Then  'not sure how to update

    'Stop events from running
    Application.EnableEvents = False

    'Column for the date/time - is row 'done' (completely priced), poss. need an IF
    If Range("C" & Target.Row) = "done" Then
    Set myDateTimeRangeDone = Range("V" & Target.Row)
    End If


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

    'Determine if the input date/time should change
    If myDateTimeRangeDone.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

 

[Joe4]

The issue is if the value in column C is not "done", then myDateTimeRange is not set to anything.
So, when you try to take its value of an empty range, you will get an error.

You should change the line:

If myDateTimeRange.Value = "" Then

to:

If myDateTimeRange Is Nothing Then

 

[dmj120]

Doing just this update gives the same object error with the next line

myDateTimeRange.Value = Date

The "updated" entry in Col. W works perfectly.

So is the issue now something with myDateTimeRange.Value = Date not 'knowing to' enter the date once "done" is populated?

    'Column for the date/time
    If Range("C" & Target.Row) = "done" Then
        Set myDateTimeRange = Range("V" & Target.Row)
    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

 

[Joe4]

Doing just this update gives the same object error with the next line

myDateTimeRange.Value = Date

The "updated" entry in Col. W works perfectly.

So is the issue now something with myDateTimeRange.Value = Date not 'knowing to' enter the date once "done" is populated?

    'Column for the date/time
    If Range("C" & Target.Row) = "done" Then
        Set myDateTimeRange = Range("V" & Target.Row)
    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

I confess I didn't look further down the code, or else that would have stuck out to me.

It is important in understanding what you are doing there in your code.
myDateTimeRange is declared to be a range. So it is NOT a value, it is a cell reference.

In you first IF is not met (column C is not "done"), then that range variable is NOT being set to anything (if column C is "done, then that range variable is being set to column V of that row).
If that range variable is not set to any range, then trying to set its value is going to result in an error (you cannot set an empty range equal to a value)!

So, I guess the question for you is this: The the value in column C is NOT set to "done", what exactly do you want to happen?

 

[dmj120]

So, I guess the question for you is this: The the value in column C is NOT set to "done", what exactly do you want to happen?

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?

The long and short of it is: Is column C is NOT "done" nothing should happen. Once column C is populated with "done" (via the formula I have) then column V should have the date.