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 said:
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
:
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 accunt 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
:
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
:
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
:
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.
