need some help please

[elias@prespa]

hello everyone

i have this code in vb


Function DateAndTime()

DateAndTime = Now

End Function


which i used to create a static date and time in this formula


=IF(K12="","Deb",IF(K12="Pag.",DateAndTime(),IF(K12="Larg.",DateAndTime())))


and it works ok but when i filter the collumn by date all the dates on the workbook change to current date and time.

How can i stop this happening?

 

[SirJB7]

Hi, eliasprespa!

Let me assure I understood the problem. If you have that formula in several rows of the column, whenever you filter it, the contents will be updated with the value of now() -if the conditions are verified, of course, that's to say every date corresponding to K12 with "Pag." or "Larg." will change it value to now.

In other words, that VBA codes has the same static status as a F1 car during a race: nothing of static.

In order to create static values you should replace the Function for a Sub, and in that sub write "worksheets(X).cells(Y,Z).value=now()", where Z is the column where you first placed the UDF. So you'll get the value of now() and not a function that get the cell recalculated each time you filter.

But I think that if you don't update each Z column from each Y row at different moments, you'll get very similar (or identical) values for that Z column.

Regards!

 

[NARAYANK991]

Hi ,


Are you thinking of timestamps ? If so , you might find the following link useful :


http://chandoo.org/wp/2009/01/08/timestamps-excel-formula-help/


Narayan

 

[elias@prespa]

thank you for your reply SirJB7 and to you NARAYANK991

the problem is that i'm not that good in vba codes. I like your idea SirJB7 and i would appreciate your help if you could possibbly write a vba code for me please.

 

[SirJB7]

Hi, elias@prespa!

Look at this and check if it's suitable for you.

http://www.2shared.com/file/nW5nK-7O/Conditional_date_stamping_from.html

It works supervising the cell modified each time in sheet 1, and if it's from column K and row from 2 in advance, it performs the update of another cell in the same row, stamping the date and time of now().

Hope it does the job.

Regards!

 

[elias@prespa]

helo again SirJB7


I tried to get the file from the address you posted but nothing happend. I couldnt download it.

Anyway thanks for your time

 

[SirJB7]

Hi, elias@prespa!

I tried downloading it from this PC and from a netbook and it all went fine. Maybe it's a problem of your firewall with 2shared.com, so I'll re-upload at another service.

http://www.megaupload.com/?d=9RWSSNPU

Try this in megaupload.com, if you can't, check your network firewall policies with your net admin.

Regards!

 

[elias@prespa]

thank you very much SirJB7!

I got the file and i'll try it.