can you help?
From: jeff (anonymous_at_discussions.microsoft.com)
Date: 07/22/04
- Next message: dh: "Locked for Editing problem -- Solved"
- Previous message: gorkyness: "Using Excel With MailMerge?"
- In reply to: rodw: "can you help?"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 22 Jul 2004 09:00:58 -0700
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
>-----Original Message-----
>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 "Excel
>
>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 an
>
>entry will be made in A2. What he wants is when A2 gets
updated, for B2
>
>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 the
>
>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,
>Rod
>
> Attachment filename:
example.xls
>Download attachment:
http://www.excelforum.com/attachment.php?postid=624794
>---
>Message posted from http://www.ExcelForum.com/
>
>.
>
- Next message: dh: "Locked for Editing problem -- Solved"
- Previous message: gorkyness: "Using Excel With MailMerge?"
- In reply to: rodw: "can you help?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
