Hello,

I have a worksheet in which a cell formula is not the ideal solution and a
macro would seem the better answer. In the columns with the date I have a
formula which returns the current date plus days. Column A has values which
change daily. I need a macro that ideally would, if it was the current date
in the date columns, place the value of column A in the price cell for that
day and not change that value on subsequent days. There date/price columns
go through plus 20 days. Currently I have the formula =IF(TODAY()=B4,A4,0)
in cell C4, the problem with which it changes the value daily and I have not
come up with a cell formula that will place the value of A4 in C4 without
changing it daily. Thank you in advance for any solutions.

(Hope this formats properly in the post to the newsgroup)

Column A ColumnB ColumnC ColumnD ColumnE ColumnF
ColumnG


Plus 1 Day

Plus 2 Days

Plus 3 Days


Price
Date
Price
Date
Price
Date
Price

$6.46
04/08/05
$0.00
4/11/05
$0.00
4/12/05
$0.00

$0.00
01/02/00
$0.00
1/3/00
$0.00
1/4/00
$0.00

$0.00
01/02/00
$0.00
1/3/00
$0.00
1/4/00
$0.00

$0.00
01/02/00
$0.00
1/3/00
$0.00
1/4/00
$0.00

$0.00
01/02/00
$0.00
1/3/00
$0.00
1/4/00
$0.00

$0.00
01/02/00
$0.00
1/3/00
$0.00
1/4/00
$0.00

$0.00
01/02/00
$0.00
1/3/00
$0.00
1/4/00
$0.00

You could try event code

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Range("B4:B23")) Is Nothing Then
With Target
If .Value = Date + .Row - 4 Then
.Offset(0, 1).Value = .Offset(0, -1).Value
Else
.Offset(0, 1).Value = 0
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"LLr" wrote in message
...
Hello,

I have a worksheet in which a cell formula is not the ideal solution and a
macro would seem the better answer. In the columns with the date I have a
formula which returns the current date plus days. Column A has values
which
change daily. I need a macro that ideally would, if it was the current
date
in the date columns, place the value of column A in the price cell for
that
day and not change that value on subsequent days. There date/price columns
go through plus 20 days. Currently I have the formula =IF(TODAY()=B4,A4,0)
in cell C4, the problem with which it changes the value daily and I have
not
come up with a cell formula that will place the value of A4 in C4 without
changing it daily. Thank you in advance for any solutions.

(Hope this formats properly in the post to the newsgroup)

Column A ColumnB ColumnC ColumnD ColumnE
ColumnF
ColumnG


Plus 1 Day

Plus 2 Days

Plus 3 Days


Price
Date
Price
Date
Price
Date
Price

$6.46
04/08/05
$0.00
4/11/05
$0.00
4/12/05
$0.00

$0.00
01/02/00
$0.00
1/3/00
$0.00
1/4/00
$0.00

$0.00
01/02/00
$0.00
1/3/00
$0.00
1/4/00
$0.00

$0.00
01/02/00
$0.00
1/3/00
$0.00
1/4/00
$0.00

$0.00
01/02/00
$0.00
1/3/00
$0.00
1/4/00
$0.00

$0.00
01/02/00
$0.00
1/3/00
$0.00
1/4/00
$0.00

$0.00
01/02/00
$0.00
1/3/00
$0.00
1/4/00
$0.00

Thanks for the response! I will try that this weekend.