Insert date in next empty cell

[Mötley]

Hello,

how do I modify this code so that it inserts new date in next available cell?

Sub SetDate()
'Set cell D2 to the current date
ActiveSheet.Range("AS23").Value = Date
End Sub

For example, I add todays date in cell AS23, tomorrow I add new date and it will appear on AT23 and so on

 

[Trevor G]

What about this

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Macro1()<br><SPAN style="color:#007F00">'using xlLastcell and offset 1 column</SPAN><br>    ActiveCell.SpecialCells(xlLastCell).Select<br>    ActiveCell.Offset(0, 1) = <SPAN style="color:#00007F">Date</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Mötley]

Thanks, that works great, just like I wanted! But is it possible that I can decide what cell I want to fill? Like that AS23 like I said in previous message?

 

[Trevor G]

You could use an inputbox so you get a prompt first and then you type in the cell reference (Like using the goto command) and then it adds the date, is that what you mean.



As you mentiond the last cell.

Do you mean the last cell in a particular row, like 23 and the next available column?

 

[Mötley]

Umm... I mean like this,

I attach the code into commandbutton, it adds specific date into cell, let's say A23, then tomorrow B24, then day after C24 etc etc

 

[Trevor G]

How are you looking to specify which cell as you have indicated row 23 then row 24?

 

[Mötley]

Oh i'm sorry! NOT row 24! I mean B23, C23 etc

 

[Trevor G]

Try this then.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> mcrGoTo()<br>Range("IV23").Select<br>ActiveCell.End(xlToLeft).Offset(0, 1) = <SPAN style="color:#00007F">Date</SPAN><br>Range("A2").Select<br><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Mötley]

It works but why i cannot change the cell where to put date? It always puts it in AI23

EDIT: Nevermind