Hi to @ll,
I have a one querry if any one can solve it I will be very thankful. my querry is that i have a file containing name of co., month statemnt received, stamped & date (on which the statement is received). what i want is that when i write yes in B coloumn of particular co. let say.A Co. which is in cell A6 so that the (yes written in B6) then in cell D6 it should get automatically today's date. I have tried one formula in D coloumn (i.e. if(cell="yes",today(),""), here cell means cell of B coloumn's cell no.). But the problem is that when I open the file next day it recalculate the formula and the date gets change to today's date
(in D coloumn ) where i have mention yes in coloumn B. If any one can write macro in such a way that when i open the file it should not recalculate the formula to those cell of D coloumn which had already returned the date in accordance with the detail in B coloumn.

Attached Files

sample file.xls (22.0 KB, 14 views)

Hi,
one suggestion to avoid a macro would be that you after entering the yes, steps two cells to the right (= to column D).

In this cell press CTRL and semicolon, which will enter todays date as a fix value in that cell.

Kind regards
Bjorn

Hi again,
You can also create this macro yourself !!

Do like this:

Put cursor in cell B6. Start recording a macro (tools / macro / record...). By clicking on a symbol on the little "macro recorder box" (the one with the stop sign) you shift to "relativ references" (which will be needed here).

Type "yes" (without enter), followed by "arrow right" two times. Now you should by in cell D6. Press CTRL + semicolon.

Stop the recording. Your macro is now ready!

A suggestion is that you assign the macro with a shortcut (for example CTRL + Y).

Now you can just place the cursor in column B (B8 for example) and press CTRL+Y and the job is done!

Kind regards
Bjorn

Thanks Bjorn for ur help,
I have created the simple macro as u suggested, but the problem is that when i run the macro by using the shortcut keeping the cursor in B coloumn's (let us take B8 ) cell then this cell's data get's update to yes but the in D coloumn ( i.e D8 ) won't get update to date. plz help...

Hi again,
I was in such a hurry before that I didn't check the macro properly

Try this instead (as you see I just quickly edited my previous instruction):

Put cursor in cell B6. Start recording a macro (tools / macro / record...). By clicking on a symbol on the little "macro recorder box" (the one with the stop sign) you shift to "relativ references" (which will be needed here).

Type "yes" (without enter), followed by "arrow right" two times. Now you should by in cell D6.

Now enter the formula =today() in the cell D6. Then (still in D6) run the command "Copy" and after this Edit / Paste special / Values. After this press escape (to hide the area marked for copying).

Stop the recording. Your macro is now ready!

A suggestion is that you assign the macro with a shortcut (for example CTRL + Y).

Now you can just place the cursor in column B (B8 for example) and press CTRL+Y and the job is done!

I really hope it works this time

Kind regards
Bjorn

Thanks Bijorn for ur help,
one more querry that instead of runing macro more often on this file, if u can suggest such thing that when i open the particular file the cell which had returned the date in d column (whatever may be the date) then it should not update the date to today's date. (let say i have mention yes in some cell e.g 10 cells (today), then those 10 cell's d column should show today's date. if I open this file next day then while calculating the formula when u open the file it should ignore those cell in d column which had already mentioned the date)
Thnks Again.....

HI,
creating such a macro would be quite tricky, and on the top of that, you might be needed to adjust the macro yourself if your cells are moved or extended in any way. It is also a solution which might be a little unsure, since any type of mistake/error/systemproblems etc, might cause the date to change to todays date by "accident".

I am not sure what you want to achieve by your new question. I find it even easier to press "CTRL+Y" in a cell to get the complete job done (including the date), instead of typing "Yes" followed by an enter (and in addition have a really complex macro to maintain/write).

I suggest that you stay with the short and easy macro

Kind regards
Bjorn

Thanks again Bjorn for ur valuable help. This had really helped me.

I have some issue with the macro, after creating macro when i use the shortcut (ctrl+Y, as per ur suggestion) the cell in which the cursor is it update with yes but the D cell of particular row won't get update to the date, instead of that the cursor goes to the cell (in which the today() formula was used n then converted into value while writing macro). so, ultimately while pressing ctrl+y the only B column get update to yes & not the date in d column.

Hi,
I read through my explanation and realized that one small thing was not to 100% clear: After typing =today() in the cell you should hit enter! After this continue with copy... etc.

After you have recorded this macro again, using the clarification above, try it again. If it still doesn't work, publish your recorded macro here and I will hopefully find the error.

You can find the macro details via the menu Tools / Macro / Macro... (or Alt-F8). After this select your macro and click on "Edit". Now you will see the macro and can copy-paste into a reply here.

Kind regards
Bjorn

As per suggestion i have recorded the macro again, but still having problem with the macro. below is the macro
Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+y
'
Range("K8").Select
ActiveCell.FormulaR1C1 = "YES"
Range("M8").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("M8").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

Hi again,
I have found the error.

After you have started recording the macro, you must follow this instruction carefully (immediately after the start of recording):

By clicking on a symbol on the little "macro recorder box" (the one with the stop sign) you shift to "relativ references" (which will be needed here).

Try this and I am sure it will work!

Rather than sending you a ready-made macro fom the beginning, I wanted you to try it out yourself, making it easier for you to change it in the future (or even create new recorded macros).


Good luck!
Bjorn

Hiii Bjorn,
need clarification from ur side.

Hi,
immediately after the start recording of the macro, you will see a little box showing up on the screen. This box you should click on (see the attached picture).

Kind regards
Bjorn

Attached Images

Macro-picture1.jpg (76.6 KB, 11 views)

hats off Buddy,
It works fine, may i know what "relative reference" means in Excel...