Find first empty cell in column J. Copy, paste special, value from
 

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

Find first empty cell in column J. Copy, paste special, value from
 

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

Find first empty cell in column J. Copy, paste special, value from
 

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

Find first empty cell in column J. Copy, paste special, value
 

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

Find first empty cell in column J. Copy, paste special, value
 

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

Find first empty cell in column J. Copy, paste special, value
 

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

Find first empty cell in column J. Copy, paste special, value
 

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

Find first empty cell in column J. Copy, paste special, value
 

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

Find first empty cell in column J. Copy, paste special, value
 

First "Option Explicit" is placed at the top of the module. It tells excel that
you want to be forced to declare every variable that you use in that module.

Second, each time you click the button, you want the date/time added after the
last used cell in column J?

Option Explicit
Private Sub CommandButton1_Click()
Dim NextCell As Range
with me
set nextcell = .cells(.rows.count,"J").end(xlup).offset(1,0)
end with

with nextcell
.Value = Now
.NumberFormat = "dd.mm.yyyy hh:mm"
end with

End Sub

zzxxcc wrote:

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


--

Dave Peterson

Find first empty cell in column J. Copy, paste special, value
 

Great. This worked almost right. Yes you are right about "after the last used
cell in column J". Still there are two small issue. All dates+times
previously pasted by this code will change to the newest date each time I hit
the button. I dont't want to refresh these previously inserted values. I only
want the current date+time to be pasted in the first empty cell in column J.

One more Issue:
I want to do exactly the same in column L. Therefore I copied the code,
changed to "L", but I get an VBA error message. I suspect this has something
to do with writing the code like this:

Option Explicit
Private Sub CommandButton1_Click()
Dim NextCell As Range
With Me
Set NextCell = .Cells(.Rows.Count, "J").End(xlUp).Offset(1, 0)
End With

With NextCell
.Value = Now
.NumberFormat = "dd.mm.yyyy hh:mm"
End With

End Sub

Option Explicit
Private Sub CommandButton2_Click()
Dim NextCell As Range
With Me
Set NextCell = .Cells(.Rows.Count, "J").End(xlUp).Offset(1, 0)
End With

With NextCell
.Value = Now
.NumberFormat = "dd.mm.yyyy hh:mm"
End With

End Sub

There is a line under each "Option explicit".
--
Thanks


"Dave Peterson" wrote:

First "Option Explicit" is placed at the top of the module. It tells excel that
you want to be forced to declare every variable that you use in that module.

Second, each time you click the button, you want the date/time added after the
last used cell in column J?

Option Explicit
Private Sub CommandButton1_Click()
Dim NextCell As Range
with me
set nextcell = .cells(.rows.count,"J").end(xlup).offset(1,0)
end with

with nextcell
.Value = Now
.NumberFormat = "dd.mm.yyyy hh:mm"
end with

End Sub

zzxxcc wrote:

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


--

Dave Peterson

Find first empty cell in column J. Copy, paste special, value
 

1. There's nothing in the code that affects the previous entries. Maybe you
have formulas that need to be converted to values -- or maybe you have something
else running that's changing them.

2. Put "Option Explicit" at the top of the module--it only belongs there a
single time. Don't add it more than once.

3. Make sure you change the column that gets the date/time in your code. Both
button_click's are looking at column J with this line:

Set NextCell = .Cells(.Rows.Count, "J").End(xlUp).Offset(1, 0)



zzxxcc wrote:

Great. This worked almost right. Yes you are right about "after the last used
cell in column J". Still there are two small issue. All dates+times
previously pasted by this code will change to the newest date each time I hit
the button. I dont't want to refresh these previously inserted values. I only
want the current date+time to be pasted in the first empty cell in column J.

One more Issue:
I want to do exactly the same in column L. Therefore I copied the code,
changed to "L", but I get an VBA error message. I suspect this has something
to do with writing the code like this:

Option Explicit
Private Sub CommandButton1_Click()
Dim NextCell As Range
With Me
Set NextCell = .Cells(.Rows.Count, "J").End(xlUp).Offset(1, 0)
End With

With NextCell
.Value = Now
.NumberFormat = "dd.mm.yyyy hh:mm"
End With

End Sub

Option Explicit
Private Sub CommandButton2_Click()
Dim NextCell As Range
With Me
Set NextCell = .Cells(.Rows.Count, "J").End(xlUp).Offset(1, 0)
End With

With NextCell
.Value = Now
.NumberFormat = "dd.mm.yyyy hh:mm"
End With

End Sub

There is a line under each "Option explicit".
--
Thanks

"Dave Peterson" wrote:

First "Option Explicit" is placed at the top of the module. It tells excel that
you want to be forced to declare every variable that you use in that module.

Second, each time you click the button, you want the date/time added after the
last used cell in column J?

Option Explicit
Private Sub CommandButton1_Click()
Dim NextCell As Range
with me
set nextcell = .cells(.rows.count,"J").end(xlup).offset(1,0)
end with

with nextcell
.Value = Now
.NumberFormat = "dd.mm.yyyy hh:mm"
end with

End Sub

zzxxcc wrote:

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


--

Dave Peterson


--

Dave Peterson

Find first empty cell in column J. Copy, paste special, value
 

Dave,
Now you have saved my day! It worked perfectly. I really appreciate your
patient with me here.
The "J" vs. "L" was just a typo when wrote the reply, but not in my code in
Excel. The "Option explicit" only in the top made a difference. The cange of
all dates previously entered was related to a misplaced formula. I think I
will go out and by a Excel Programming book soon.
--
Thanks


Dave Peterson skrev:

1. There's nothing in the code that affects the previous entries. Maybe you
have formulas that need to be converted to values -- or maybe you have something
else running that's changing them.

2. Put "Option Explicit" at the top of the module--it only belongs there a
single time. Don't add it more than once.

3. Make sure you change the column that gets the date/time in your code. Both
button_click's are looking at column J with this line:

Set NextCell = .Cells(.Rows.Count, "J").End(xlUp).Offset(1, 0)



zzxxcc wrote:

Great. This worked almost right. Yes you are right about "after the last used
cell in column J". Still there are two small issue. All dates+times
previously pasted by this code will change to the newest date each time I hit
the button. I dont't want to refresh these previously inserted values. I only
want the current date+time to be pasted in the first empty cell in column J.

One more Issue:
I want to do exactly the same in column L. Therefore I copied the code,
changed to "L", but I get an VBA error message. I suspect this has something
to do with writing the code like this:

Option Explicit
Private Sub CommandButton1_Click()
Dim NextCell As Range
With Me
Set NextCell = .Cells(.Rows.Count, "J").End(xlUp).Offset(1, 0)
End With

With NextCell
.Value = Now
.NumberFormat = "dd.mm.yyyy hh:mm"
End With

End Sub

Option Explicit
Private Sub CommandButton2_Click()
Dim NextCell As Range
With Me
Set NextCell = .Cells(.Rows.Count, "J").End(xlUp).Offset(1, 0)
End With

With NextCell
.Value = Now
.NumberFormat = "dd.mm.yyyy hh:mm"
End With

End Sub

There is a line under each "Option explicit".
--
Thanks

"Dave Peterson" wrote:

First "Option Explicit" is placed at the top of the module. It tells excel that
you want to be forced to declare every variable that you use in that module.

Second, each time you click the button, you want the date/time added after the
last used cell in column J?

Option Explicit
Private Sub CommandButton1_Click()
Dim NextCell As Range
with me
set nextcell = .cells(.rows.count,"J").end(xlup).offset(1,0)
end with

with nextcell
.Value = Now
.NumberFormat = "dd.mm.yyyy hh:mm"
end with

End Sub

zzxxcc wrote:

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


--

Dave Peterson


--

Dave Peterson

Find first empty cell in column J. Copy, paste special, value
 

Glad you got it working.

zzxxcc wrote:

Dave,
Now you have saved my day! It worked perfectly. I really appreciate your
patient with me here.
The "J" vs. "L" was just a typo when wrote the reply, but not in my code in
Excel. The "Option explicit" only in the top made a difference. The cange of
all dates previously entered was related to a misplaced formula. I think I
will go out and by a Excel Programming book soon.
--
Thanks

Dave Peterson skrev:

1. There's nothing in the code that affects the previous entries. Maybe you
have formulas that need to be converted to values -- or maybe you have something
else running that's changing them.

2. Put "Option Explicit" at the top of the module--it only belongs there a
single time. Don't add it more than once.

3. Make sure you change the column that gets the date/time in your code. Both
button_click's are looking at column J with this line:

Set NextCell = .Cells(.Rows.Count, "J").End(xlUp).Offset(1, 0)



zzxxcc wrote:

Great. This worked almost right. Yes you are right about "after the last used
cell in column J". Still there are two small issue. All dates+times
previously pasted by this code will change to the newest date each time I hit
the button. I dont't want to refresh these previously inserted values. I only
want the current date+time to be pasted in the first empty cell in column J.

One more Issue:
I want to do exactly the same in column L. Therefore I copied the code,
changed to "L", but I get an VBA error message. I suspect this has something
to do with writing the code like this:

Option Explicit
Private Sub CommandButton1_Click()
Dim NextCell As Range
With Me
Set NextCell = .Cells(.Rows.Count, "J").End(xlUp).Offset(1, 0)
End With

With NextCell
.Value = Now
.NumberFormat = "dd.mm.yyyy hh:mm"
End With

End Sub

Option Explicit
Private Sub CommandButton2_Click()
Dim NextCell As Range
With Me
Set NextCell = .Cells(.Rows.Count, "J").End(xlUp).Offset(1, 0)
End With

With NextCell
.Value = Now
.NumberFormat = "dd.mm.yyyy hh:mm"
End With

End Sub

There is a line under each "Option explicit".
--
Thanks

"Dave Peterson" wrote:

First "Option Explicit" is placed at the top of the module. It tells excel that
you want to be forced to declare every variable that you use in that module.

Second, each time you click the button, you want the date/time added after the
last used cell in column J?

Option Explicit
Private Sub CommandButton1_Click()
Dim NextCell As Range
with me
set nextcell = .cells(.rows.count,"J").end(xlup).offset(1,0)
end with

with nextcell
.Value = Now
.NumberFormat = "dd.mm.yyyy hh:mm"
end with

End Sub

zzxxcc wrote:

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


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson