Recording Cell Value

[Forever Forest]

Hi all,

I have just used the following code, from this forum (Someone called Alan - Thanks Alan whoever you are!!), that does what I'm looking for.

My only problem is, the code works fine if the data I enter into A1 is manual. But if the value in A1 in coming from elsewhere in the sheet, I can't get the code below to work.

Does anyone have any ideas how I might try and solve this at all?

(many thanks in advance)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lRow As Long
Dim vaData() As Variant
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub

ReDim vaData(1 To 1, 1 To 3)
vaData(1, 1) = Now()
vaData(1, 2) = Target.Address
vaData(1, 3) = Target.Resize(1, 1).Value
Application.EnableEvents = False
With Sheets("Sheet2")
lRow = .Cells(Rows.Count, "A").End(xlUp).Row + 1
.Range("A" & lRow & ":C" & lRow).Value = vaData
End With
Application.EnableEvents = True
End Sub

 

[Fluff]

Hi & welcome to MrExcel.
Does this work for you

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim keyCells As Range
   Set keyCells = Range("A1")
   On Error Resume Next
   Set keyCells = Application.Union(keyCells, keyCells.Precedents)
   On Error GoTo 0
   If Not Application.Intersect(keyCells, Target) Is Nothing Then
      With Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(, 3)
         .Value = Array(Now, "$A$1", Me.Range("A1").Value)
      End With
   End If
End Sub

 

[Forever Forest]

Hi & welcome to MrExcel.
Does this work for you

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim keyCells As Range
   Set keyCells = Range("A1")
   On Error Resume Next
   Set keyCells = Application.Union(keyCells, keyCells.Precedents)
   On Error GoTo 0
   If Not Application.Intersect(keyCells, Target) Is Nothing Then
      With Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(, 3)
         .Value = Array(Now, "$A$1", Me.Range("A1").Value)
      End With
   End If
End Sub

It does indeed! Thank you so much Fluff.

 

[Fluff]

You're welcome & thanks for the feedback

 

[Forever Forest]

Just one last question Fluff - if the data coming in to A1 is automated, and updating quite often, will this code still record that in Sheet 2? Or does it only work if I enter a value and manually press return/enter?

 

[Fluff]

That should work whenever a cell that A1 depends on is changed.