C0d£ !!!!

[tommyboy]

I have this code and works fine to update colum a with any new data i
b4

Private prev As Variant


Private Sub Worksheet_Calculate()
Static init As Boolean
Dim v As Variant

Application.EnableEvents = False
On Error GoTo CleanUp

v = Me.Range("B4").Value

If init And v <> prev Then
Sheets("sheet17").Cells(Rows.Count, "A"). _
End(xlUp).Offset(1, 0).Value = v

prev = v

ElseIf Not init Then
init = True
prev = Range("B4").Value

End If

CleanUp:
Application.EnableEvents = True
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$4" Then Exit Sub

Application.EnableEvents = False
On Error GoTo CleanUp

prev = Target.Value

Sheets("sheet17").Cells(Rows.Count, "A"). _
End(xlUp).Offset(1, 0).Value = prev

CleanUp:
Application.EnableEvents = True
End Sub

When updating colum A with new data is it possible to also update th
coresponding column B, with the curent data in F4 and update th
coresponding colum C with the current time.

I have looked at your code but cant see how to duplicate for othee
line

 

[Don Guillett]

This should be all you need. I noticed a later thread. It's always best to
stay in the ORIGINAL.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$4" Then Exit Sub

Application.EnableEvents = False
On Error GoTo CleanUp

with Sheets("sheet17")
x=.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
cells(x,1)=target
cells(x,2)=range("f4")
cells(x,3)=time
end with

CleanUp:
Application.EnableEvents = True
End Sub

 

[tommyboy]

this doesnt seem to work

still updates column a but not b or

 

[Don Guillett]

try it this way. I forgot the . before cells in the with. As written now, it
will put the values in sheet 17 from this macro in your current sheet with
the f4 also in the current sheet. Is that what you want.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$4" Then Exit Sub

Application.EnableEvents = False
On Error GoTo CleanUp

With Sheets("sheet17")
x = .Cells(Rows.Count, "A").End(xlUp).Row + 1
..Cells(x, 1) = Target
..Cells(x, 2) = Range("f4")
..Cells(x, 3) = Time
End With

CleanUp:
Application.EnableEvents = True
End Sub


--
Don Guillett
SalesAid Software
(e-mail address removed)

This should be all you need. I noticed a later thread. It's always best to
stay in the ORIGINAL.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$4" Then Exit Sub

Application.EnableEvents = False
On Error GoTo CleanUp

with Sheets("sheet17")
x=.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
cells(x,1)=target
cells(x,2)=range("f4")
cells(x,3)=time
end with

CleanUp:
Application.EnableEvents = True
End Sub

 

[tommyboy]

First i have to say thanks for all the help as you have been
lifesaver

this does work but only when cell b4 is changed by user edit so when i
is the result of a calculation it only updates column a.



I cant see why this is

any ideas?

Private prev As Variant


Private Sub Worksheet_Calculate()
Static init As Boolean
Dim v As Variant

Application.EnableEvents = False
On Error GoTo CleanUp

v = Me.Range("E4").Value

If init And v <> prev Then
Cells(Rows.Count, "A"). _
End(xlUp).Offset(1, 0).Value = v

prev = v

ElseIf Not init Then
init = True
prev = Range("E4").Value

End If

CleanUp:
Application.EnableEvents = True
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$E$4" Then Exit Sub

Application.EnableEvents = False
On Error GoTo CleanUp

With Sheets("sheet17")
x = .Cells(Rows.Count, "A").End(xlUp).Row + 1
.Cells(x, 1) = Target
.Cells(x, 2) = Range("F4")
.Cells(x, 3) = Time
End With

CleanUp:
Application.EnableEvents = True
End Sub

 

[tommyboy]

any help