can you help?

  • Thread starter Thread starter rodw
  • Start date Start date
R

rodw

Can anyone help? This is a follow on from a question I posted a few of
weeks ago on behalf of my son.

He's got a spreadsheet and is using some code which updates column A
when cell E4 changes to a different value as a result of a calculation


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
Sheets("sheet17").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

prev = Target.Value

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

CleanUp:
Application.EnableEvents = True
End Sub

I've uploaded the spreadsheet to btintra.test with a subject of "Exce

test" and what he wants to do is this -
If you type something into H4 it updates E4
Type something different into H4 then E4 will change and as a result a

entry will be made in A2. What he wants is when A2 gets updated, for B

to take on the value of F4, and C2 to be updated with the time.
Likewise when something different is entered into H4, then E4 will
change and an entry will then be made in A3. He wants B3 to take on th

value of F4, and C3 to be updated with the time.
And so on for A4.......

Can this be done? Any help would be much appreciated.

Thanks,
Ro

Attachment filename: example.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=62479
 
Hi, Rod,

I think this code comes close: I changed E4 to
=$H$4.

good luck
jeff


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
Sheets("sheet17").Cells(Rows.Count, "A"). _
End(xlUp).Offset(1, 0).Value = v
Sheets("sheet17").Cells(Rows.Count, "B"). _
End(xlUp).Offset(1, 0).Value = Range("$f$4").Value
Sheets("sheet17").Cells(Rows.Count, "C"). _
End(xlUp).Offset(1, 0).Value = Now()
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 <> "$H$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
' Sheets("sheet17").Cells(Rows.Count, "B"). _
' End(xlUp).Offset(1, 0).Value = Range
("$f$4").Value
' Sheets("sheet17").Cells(Rows.Count, "C"). _
' End(xlUp).Offset(1, 0).Value = Now()

CleanUp:
Application.EnableEvents = True
End Sub
 
Back
Top