update a value only TODAY

[anxoperez]

I have a value in A1 which fluctuates.

I have 2 columns--DATE and REGISTRY. I have a macro that records each DATE and its corresponding value under REGISTRY. The corresponding value is whatever appears in cell A1 for that date.

What I need is for that value under REGISTRY to also fluctuate just like in A1 but ONLY throughout THAT DATE. Once the date is over (or the document is closed), I need it to freeze the value (whatever value it showed last).

Can anyone help me with this?

 

[venkat1926]

on the first day you enter the date in some cell in column A e.g. A10 and B10 enter the registry

rightclick the sheet tab at the bottom and click view code and there copy this code
now subsequent days the entreis may be made as you want.

do this in a experimental sheet and chek . For experiment pruposes the first day may be yesterday and entered in A10 and some reegistry value in B10
now change the value in A1 and see what happens
again change the vallue in A1 and see new value is entred in todays date in column B
check

the event code is

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
Dim rng As Range
Set rng = Cells(Rows.Count, "a").End(xlUp)
If Date <> rng Then
Set rng = rng.Offset(1, 0)
rng = Date
rng = rng.Value
rng.Offset(0, 1) = Range("a1")
Else
rng.Offset(0, 1) = Range("a1")
End If
End Sub

 

[anxoperez]

I have too many questions.

First, I can't really do anything because I am unable to figure anything out out of what you wrote. My arrangement on the worksheet is not exactly as I think you understood it, and if I could understand a bit more, I'm sure it would just be a matter of adjusting a couple of things, but I don't.

Let me explain you what I have.


I have 4 columns. I have the header row in row number 4. the first column contains today's date (volatile) (column a). Column B is unimportant and only has 1 row below the header. Column C has the DATES. And column D has the REGISTRIES. Both C and D could be long, i.e. have a lot of rows.

So, maybe now you can either adjust your formula, explain the formula to me so I can make the adjustments (assuming I'd be able), or if you were so kind, both.

Finally. I understand this is not a macro that needs to be run but rather it is updated continuously?

By the way, the name of the worksheet is "Rank". I don't know if that's important as well.

Thanks a million!!

 

[venkat1926]

will it be possible for you to post in the newsgroup your sheet only with about ten rows. use html maker of this newsgsroup (see the sticky threds at the beginning of the list of postings)

 

[anxoperez]

Venkat, I have been reading up on how to post HTML. Man, it looks pretty hard and I feel quite illeterate.

Do you think we could try it without the HTML. The thing is, what I have is extremely simple. You were thrown off by the fact that I said there were 2 other columns, but you could almost ignore that.

It looks sort of like this:

Zhong1 wen2, tests.xls
	A	B	C	D	E	F	G
1
2
3
4	TODAY	dailyload	DATES	POSITION
5	9-Jun-08	1/5	5-Jun-08	0,00
6			6-Jun-08	-0,20
7
8
9
10
11
12
13
14
15
16
17
Rank

 

[anxoperez]

oops, forgot to include the A1 cell.

Now it is more accurate.<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis...[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE]

 

[anxoperez]

I have no idea why the second paste looks so bad while the first looks so good. It would be nice to know for the future...

 

[venkat1926]

I am little experimenting.; so do this in a copied file so that the original is not messed up

now righlcik the excel icon to the left of "file" in the menu bar and clivk view code. In the window that comes up copy the macro (event code) . now do not save it, but wait for the end of the day and then save it first (control+S).(DO NOT SAVE IN THE MIDDLE OF THE DAY). see what happens in col C and D
then close it . if by chance if the dialog box "do you want to save" comeS up afer once you have saved click "no"
there is no need for any formulas in col C and D except on the first day.

AM I CLEAR IN MY INSTRUCTIONS? if there is bug or error tell me at what code statemnt the error occurs and f possible the error message.

I do not why that repetion in A1

the macro(event code ) is

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim rng As Range
With Worksheets("rank")  ' if the sheet name is differnt change it.
Set rng = .Cells(Rows.Count, "c").End(xlUp).Offset(1, 0)
rng = Range("a5")
rng.Offset(0, 1) = rng.Offset(-1, 1) - Range("B5")
.Columns("c:c").NumberFormat = "dd-mmm-yy"
End With
End Sub

give me feedback and if there are any doubts let us try to solve it.

 

[anxoperez]

hi again.

the thing is, the formulas under column D (Registry) should be disregarded (they are only for days when the doc was not opened). Once TODAY is reached (DATE column = A5), I need Today's registry, i.e. D6, to be equal to 'THE LIST'!I2 (another cell in a different worksheet), and when that date is no longer today (or if the document is closed while it is still today), then I need to freeze whatever was the last registry shown for that date.

summary:

If last date = TODAY then last registry = 'THE LIST'!I2

else, freeze the last value shown under registry and move on to the new row which will be the new Today. Now its registry should = 'THE LIST'!I2

Here's the table again, improved a bit.

clearer?

Note: you are right about the repetition in A1. it was unnecessary. it's gone now.<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis...[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE]

 

[venkat1926]

did you try the event code . did it not succeed. what was the problem.

when you save the file at the end of the day automatically the event code does what you wnat?