An Excel Impossibility?!???!??

[Roccofan]

I have VBA code that copies the contents on one cell an pastes the values in another cell after the contents of a third cell is changed. What I would like to do is capture the contents of a cell before it changes so that I can measure the change.

To be more specific: I have a date in cell A2. That date changes each time I change the data in cell A1. I would like to have the data in A2 copied and pasted as a hard value into cell A3 BEFORE the change occurs in cell A2.

The more I think about it the more I'm convinced that I'll have to have the data copied to another part of the spreadsheet and then reference that in cell A3. I'll probably need to have code that continually places the contents of cell A2 in a range range of cells (lets say A100....A200). I can then have Cell A3 reference the most recent addition or the next to most recent addition.

Any help would be appreciated.

RF
This message was edited by Roccofan on 2002-08-28 11:11

 

[stevebausch]

The whole sheet's values are to be monitored, or just a handfull of cells?

I suggest your code will reside in Worksheet_Change, using the Intersect method to determine if Target is one of your desired cells.

If Target is cell you want to monitor, refer to an array (you created), and see what that cell value was previously.

You just need to Dim the array to an appropriate size. Since the cell values are variants, Dim As Variant, or just don't declare a type, and the array will default to As Variant.

 

[klb]

Try this scenario:

When A1 changes, copy/paste special A2 to A3
When A3 changes, copy A1 to A2

 

[Juan Pablo González]

This is kind of cheating, but I think it works.

It adds a comment, then "restores" the old value, instead of the "new" value. Try it out.

<pre>Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
On Error Resume Next
Application.EnableEvents = False
With Range("A1")
Range("C65536").End(xlUp).Offset(1).Value = .Comment.Text + 0
.Comment.Delete
.AddComment CStr(Range("B1").Value)
'.Comment.Text Range("B1").Value
End With
Application.EnableEvents = True
End If
End Sub</pre>

I monitor cell A1, the formula is in B1, and I store the values in column C

 

[Roccofan]

Juan,

How do you paste your code so that it's so neat?

Everyone:

This is the code I used and it works. I'll try your suggestions and let you know. I "borrowed" the following code from a couple of other macros...hey, how else am I going to learn this stuff :

Dim p(1)
Dim v(1)

If Target.Address = "$B$13" Then
Sheets("MB Assumptions").Activate
Range("F115").Select
Selection.End(xlDown).Select

' Establish what row the next row is

nr = ActiveCell.Row + 1
p(1) = "F" & nr

' Load the values from the input forms to variables v1

Sheets("MB Assumptions").Activate
v(1) = Range("C13").Value

' Store the values of these variables to the places calculated above

Sheets("MB Assumptions").Activate
For n = 1 To 1
Range(p).Value = v
Range("B13").Select
Next
End If

Thanks again

RF

 

[XL-Dennis]

rf,

How do you paste your code so that it's so neat?

Take a look in the following thread:
http://www.mrexcel.com/board/viewtopic.php?topic=18125&forum=9&3

Kind regards,
Dennis

 

[Juan Pablo González]

Yes, basically same concept, just that you use public variables instead of the comment... and thanks Dennis for the help about the html tags.