Dave,
Thanks. After a few modifications it now runs and returnes date+time in
correct format, but it doesn't perform what i want it to. In J6 and downwards
I have values. No formulas. It is dates+times formated "dd.mm.yyyy hh:mm". In
K6 and down I have formulas which I don't want to change. The code actually
replaces all cells with content in column K and enters date+time. And the
value inserted in all cells are the same - the same date+time. Here is the
modified code:

Private Sub CommandButton1_Click()
Dim r As Range, r1 As Range
Set r = Columns(10).SpecialCells(xlConstants)
Set r1 = Intersect(Columns(11), r.EntireRow)
r1.Value = Now
r1.NumberFormat = "dd.mm.yyyy hh:mm"
End Sub

The "Option Explicit" text is not included in the code. It wasn't accepted.

Actually I want the code to paste the current date & time in the first empty
cell in column J. (Not K, and without chaning all values entered earlier.) So
the code should fill data in one cell further down each time it is
excecuted/run.
--
Thanks


"Dave Peterson" wrote:

There was another typo:

Option Explicit
Private Sub CommandButton1_Click()
Dim r As Range, r1 As Range
Set r = Columns(10).SpecialCells(xlConstants)
Set r1 = Intersect(Columns(11), r.EntireRow) '<-- this changed
r1.Value = Date
r1.NumberFormat = "mm/dd/yyyy"
End Sub

But you should have gotten an error when you clicked the button.

And this code only looks for values--not formulas in column J.

Do you have formulas in J?

zzxxcc wrote:

Yes. I also tried out correcting this. Now I don't get any error message.
Nothing seems to happen when clicking the button. (I am not in Design Mode).
I should mention that the values in column J starts at row 5 with heading,
and date+time in row 6 and down. All headings are in row 5. Column A to M
contains data.

Maybe the code doesn't ac**** for this?
Do I need to replace anything in the code like the letter r and r1 with
something else? ...or can I just paste this as it is:

Private Sub CommandButton1_Click()
Dim r As Range, r1 As Range
Set r = Columns(10).SpecialCells(xlConstants)
Set r1 = Intersect(Columns(11), r1.EntireRow)
r1.Value = Date
r1.NumberFormat = "mm/dd/yyyy"
End Sub
--
Thanks

"Dave Peterson" wrote:

Try Intersect instead (it was a typo).

And Tom's code doesn't get the time/date from K1. It uses VBAs equivalent (Date
was replaced with Now in a later post).




zzxxcc wrote:

Thanks Tom,
A small problem. I got an error message in the code when running it:
"Compile error: Sub or function not defined" and the word "interesect" was
highlighted.

Can you explain how this code actually collects the date and time from cell
K1?
I am using the date and time format: dd.mm.yyyy hh:mm
--
Thanks

"Tom Ogilvy" wrote:

Just notices you were using =Now() rather than =Today(). So for the code:

Replace
Date
with Now

if you want time included. Adjust the format in the "numberformat"
statement with one that formats the cell as you wish.

--
Regards,
Tom Ogilvy

"zzxxcc" wrote:

Hi,
I have searched this site for several hours, but I am not able to find the
answer I am looking for. I'm a novise om VBA - maybe that's why?

I need to [copy + paste special, value] the value of K1 [=NOW()] down to the
first empty cell in column J. Column J has got blank cells so the code needs
to ignore these blanks.

Please explain as for a VBA novise. Like: Where to put the code...
Full-text-code...

Just to explain the purpose: (I know how to do the following): Finally I
want to make a button assigned to this macro to enable a one-click operation
to paste the date/time value in the bottom of my continuously expanding list.
--
Thanks

--

Dave Peterson


--

Dave Peterson