Timestamp on data change

Hello,

I have a VBA script that is supposed to step through each cell in a range, compare it to a value in the same cell in a different sheet. If it's different then it will update the different sheet and update the time stamp. The line that errors is -

ActiveWorkbook.Sheets("Memory").Range(cell.Address) = cell.Value

The reference in the If statement works fine -

If cell.Value <> ActiveWorkbook.Sheets("Memory").Range(cell.Address).Value

I'm sure it's just me not understanding how to set a cell in another sheet?

Here's the code I'm using :

Code:

---

Function Timestamp2(Reference As Range) As Date
    For Each cell In Reference
        If cell.Value <> ActiveWorkbook.Sheets("Memory").Range(cell.Address).Value Then
            ActiveWorkbook.Sheets("Memory").Range(cell.Address) = cell.Value
            Timestamp2 = Format(Now, "yyyy-mm-dd hh:mm:ss")
        Else
            Timestamp2 = ""
        End If
    Next cell
End Function

---

Forgot to add error information. The error is

Run-time error "1004": Application-defined or object-defined error

Code:

---

ActiveWorkbook.Sheets("Memory").Range(cell.Address).Value = cell.Value

---

Does the above work?

Same error with .Value added.

Attached is a test workbook that does not get that error.

Well, that's weird.

Ok, I tested my sheet by running a sub to call the function in the VBA IDE, as in your example, and everything works fine. However, if I use the function in a cell, =Timestamp2(range) , I get the errors and it fails at the same spot?

So, jdc2000, I took your workbook, moved the code to a module and ran it from the sub. Worked fine. Then added =Timestamp2(range) to a cell in the workbook and it results in the same error in your spreadsheet.

Very strange. Possibly the way I have this set up it doesn't get write privilege to other sheets? I don't know Excel VBA well enough, obviously :).

I never try to set cell values using a Range qualifier, so I have not seen this error before.

Will the cells you are wanting to compare always be in sheets in the same workbook?
If yes, and you have selected a Range of cells, how do you know which other sheet you want to compare the cells to (and update those cells if they are different)?

Are you always comparing the same range of cells, or can that range change (other than adding additional rows)?

It's supposed to loop through the cells in the range specified and compare the values to the same cells in the sheet called Memory. If the cells are different it updates them in Memory and then sets the timestamp. Although, now that we're talking it through I see I made a mistake in my logic. I should be checking all the values and updating Memory first, then, if ANY of the values in the range had to be changed, update the timestamp.

I can rewrite that but need to understand why I'm getting my error before I can move on.

Thanks for taking the time to look into it.

Is the cell that gets the time stamp always the same, or always at the end of the sheet with the updated data?

I have an idea on how to change the code so it will work with a selected range. However, when I create macros to compare data on sheets, I generally do not set up user-defined functions that get called whenever a referenced cell value changes, since I might make a typo or change the wrong cell, which might cause an error. Instead, I change the values that need to be updated, and once all data is ready for the comparison, I run the macro and compare all cells that I want to check.

Haven't looked at the Workbook.
1) If you want to use a VBA-Function directly in a cell on a sheet, this function must be "Public" in a standard-module.
2) That said in 1): How are you passing "Reference As Range" to the Function? AFAIK, any Argument passed to a VBA-Function is either String or Numeric (Integer, Double).
Error 1004 is the "El Clasico" of referencing an Object that doesn't exist, and my money is on that Reference-Range.
So i'd look at the route of "Public Function TimeStamp2(ByVal Range As String) As Date", pass the Address as string, and then use that String to qualify your Range-Object locally.

how do you call the function? from some other procedure or as a userdefined worksheet function

if you are using it as a worksheet function then you can not read or write to other ranges and have the function return a result to the calling cell

even if from another procedure you can not have any time stamp from each cell as only the result from the last cell in the range will be returned, when the function completes

Quote:

---

Timestamp2 = ""

---

setting a date to an empty string should cause an error, type mismatch

i am a bit vague about what you are actually trying to achieve
can you post a sample workbook (zip first) so we can see what it is you are wanting to do

Thank you for the feedback. I was off work for a week and am now just getting back to this. So, attached is a sample file with the VBA code and it's use in the sheet Sheet1 cell B2. The idea is if one of the people working on this sheet changes a value in the given range it should update the date/time stamp. But, only if the value has been changed.

Also attached is a screen grab of the VBA stepping through the code. You can see in the immediate window that ActiveWorkbook.Sheets("Memory").Range(cell.Address).Value returns the correct value, as does cell.Value, in order to make the comparison. However, the code fails on the ActiveWorkbook.Sheets("Memory").Range(cell.Address).Value = cell.Value line.

Attachment 179143Attachment 179144

i am not sure i can make this work the way you are doing it, as it has several problems

you can not write to other cells from a user defined worksheet function
if you try to write to other cells the worksheet function will refresh again, which may possibly be resolved by setting enable events to false
you try to check the values of all cells (d2:f2) each time

a much better way to do this is to use the worksheet_change event

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("d2:f2")) Is Nothing Then
            If Target.Value <> ActiveWorkbook.Sheets("Memory").Range(Target.Address).Value Then
                ActiveWorkbook.Sheets("Memory").Range(Target.Address).Value = Target.Value
                Range("b2") = Format(Now, "yyyy-mm-dd hh:mm:ss")
            End If

    End If
End Sub

---

this is tested to do what i believe you want
select the worksheet change event from the dropdowns in the code module for sheet1

Ok, thank you Pete. I did try your process and it works perfectly. Much appreciated.

The further I get into this the more I think my idea for tracking changes is not so sound. When I started on it I thought it was going to be straight forward and relatively simple. There are up to 100-200 rows of this data in the actual workbook and a similar logic would have to be processed on a number of additional sheets within the workbook.

Anyone have different suggestions for accomplishing a similar concept of timestamping cell changes?

Quote:

---

Anyone have different suggestions for accomplishing a similar concept of timestamping cell changes?

---

extra columns next to the updated cells?

EDIT: another thought would be to put the timestamp into the cell comments

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("d2:f2")) Is Nothing Then
            If Target.Value <> ActiveWorkbook.Sheets("Memory").Range(Target.Address).Value Then
                With ActiveWorkbook.Sheets("Memory").Range(Target.Address)
                    .Value = Target.Value
                    If Not .Comment Is Nothing Then .Comment.Delete
                    .AddComment CStr(Now)
                End With
            End If

    End If
End Sub

---

Thanks Pete. I didn't think of this. Good idea. I'm going to play around with some different options now that you've helped me. Appreciate it. I'll post what I come up with for a final solution.