Cell Last Changed DateTime

[TiVoSoFine]

I have built my own portfolio management spreadsheet, which pulls stoc
quotes from the web. What I'd like to do is to keep track of th
historical values of the portfolio.

Specifically, this is what I'm trying to do:
1) portfolio is on worksheet WS1, with a cell (call it A1) containing
fomula which returns the current total of the whole portfolio,
2) history data is on worksheet WS2, with column A populated with
series of dates, 2/15/2004, 2/16/2004, etc., and B (to be) populate
with the A1 of WS1 of that day,
3) whenever A1 of WS1 changes, I'd like to record the value of new A
into WS2, alongside the current date.

My only technical challange left in all this is to code some kind o
"cell changed" event on A1, which will then figure out what time it i
and where to write on WS2. I'm fairly decent at programming, so an
hint will help.

Thanks very much

 

[Dave Peterson]

A1 contains a formula?

Option Explicit
Private Sub Worksheet_Calculate()

Dim LastCell As Range

With Worksheets("ws2")
Set LastCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(0, 1)
End With

With LastCell
If .Value = Me.Range("a1").Value Then
'do nothing
Else
With .Offset(1, -1) 'back to column A
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
.Offset(1, 0).Value = Me.Range("a1").Value
End If
End With

End Sub

Each time WS1 calculates, it looks to see if the last row in ws2 (based on
column A) has the same value as the current value in A1.

If it does, it stops.

If not, then it does some assignments.

 

[Dave Peterson]

A couple of links that may help you later:

Chip Pearson's site:
http://www.cpearson.com/excel/events.htm
or
David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[TiVoSoFine]

Dave,

Thanks for the links. I'll definitely check them out