Questions - New to Excel

[Iced Tea]

I have a spreadsheet set up with pricing from vendors. Here is my goal to
achieve if possible but I am not sure since I just know the basics.

Example: (this is how I have it setup now)

Description Date Price
Widget A 9/25/05 $0.04
Widget B 8/20/05 $0.75
Widget C 9/25/05 $0.15

Example: (this is how I want it to look after the fact)

Description Date Price Last Date Old Price Change %
Change
Widget A 9/30/05 $0.06 9/25/05 $0.04 +$0.02
+50.0%
Widget B 9/30/05 $0.75 8/20/05
- -
Widget C 9/30/05 $0.11 9/25/05
5 -$0.04 -26.6%


I only want to key new information into the Date cell and Price cell and
have it move what was in the prior to that to the Last Date cell and the Old
Price cell.

If this possible?

Please help,

Thanks,

Carl

 

[Dave Peterson]

How about this...

You create a macro that copies the values from the date & Price columns to the
last date & last price columns. But you run it on demand--either with a button
placed on the worksheet (row 1 with row 1 frozen so that it's always visible or
even a floating toolbar)?

I'd select a cell on that row and have the macro work with the activecell's row.

Option Explicit
Sub testme()
Dim myRow As Long

myRow = ActiveCell.Row

With ActiveSheet
If Application.CountA(.Cells(myRow, "B").Resize(1, 2)) <> 2 Then
MsgBox "Not enough info to move"
Else
.Cells(myRow, "D").Value = .Cells(myRow, "B").Value
.Cells(myRow, "E").Value = .Cells(myRow, "C").Value
.Cells(myRow, "B").ClearContents
.Cells(myRow, "C").ClearContents
.Cells(myRow, "B").Select
End If
End With

End Sub


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