Calculating once, daily

E

EO

I am trying to chart progress daily and to keep the value of each day's
progress.

A1 has the sum of column C1:C22 that is assigned points for progress. This
value changes as progress is made. Assume A1 is 10 on the 06/23 and 15 on
the 06/24.

A2 is Today().

Column B1:B22 is a date range from today to 22 days later.

Column D1:D22 is for progress

How do create a formula that when A2=B1 then D1 is A1 (06/23=06/23 then D1=
10), the next day when I open the file 06/24 the value for D1 is still 10 and
the value for D2 is 15?

The IF statement fails to work since it will update each day with new
values. My attempt was:

In cell D1: =IF(B1>=$A$2, $A$1,#N/A). This works for getting the value but
old data is written over. How/what can you do to capture the data and not
lose it?
 
S

StumpedAgain

Untested, but the following, when run daily, should do the trick (or get you
close).

Sub Capture_Progress()

Dim currentcell As Range
Set currentcell = Range("B1")

Do
If Range("A2").Value = currentcell.Value Then
currentcell.Offset(0, 2) = Range ("A1").Value
Exit Do
End If
Set currentcell = currentcell.Offset(1, 0)
Loop

End Sub
 
E

EO

I read up on Offset() but can not make sense of the rest of the instruction
below. Please simplfy if possible, thanks.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top