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

Add a commandbutton to your sheet. Then double click on it to go to the
sheet module. Put in code like this:


Private Sub CommandButton1_Click()
Dim r as Range, r1 as Range
set r = columns(10).SpecialCells(xlConstants)
set r1 = Interesect(columns(11),r1.EntireRow)
r1.Value = Date
r1.Numberformat = "mm/dd/yyyy"
End Sub

--
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

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

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

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

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

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