Macro - Selecting Next Available Cell

[cappuccine]

I posted this in the new users forum but figured it would probably best
be answered here.

I am trying to design a macro to work with a database I made in excel.
I have a lot of questions since I have never made a macro before. But
I'll just start with a simple question. I want to run a macro that will
input the current date in cell a1, then when I run the macro the next
day, I want to input the current date in cell a2. How do I set up a
macro to do that? I tried setting up a counter but I don't know how to
say something like Range(a1 + 1) = Now. So it selects the next cell
down everytime I run it. Thanks for the help in advance.

Jason

 

[Edward Ulle]

Assuming everytime you run the macro you want to add a date below the
last date entered try this.

Option Explicit

Sub Test()

Dim i As Integer

i = 0
' Assuming you want the active sheet
Do While Not IsEmpty(Range("A1").Offset(i, 0))
i = i + 1
Loop
Range("A1").Offset(i, 0) = Date$

End Sub

 

[Tom Ogilvy]

If nothing else will be below that data, you can get the next available cell
by using

Sub placeDate()
Dim rng as Range
set rng = cells(rows.count,"A").End(xlup)
if not isempty(rng) then
' check that it isn't already there
if rng.value = date then exit sub
' it isn't the current date, so place today's
' date in the next cell
set rng = rng.offset(1,0)
end if

rng.Value = Date
End Sub

 

[Don Guillett]

right click the excel x just to the left of FILE on the menu>copy/paste
this>save. Now, each time the file is opened excel will check the last cell
in col A of sheet 10. Modify to suit.

Private Sub Workbook_Open()
Set x = Sheets("sheet10").Cells(Rows.Count, "a").End(xlUp).Offset(1)
If x.Offset(-1) <> Date Then x.Value = Date
End Sub

 

[Tom Ogilvy]

Just some added INFO.
this assumes you don't want to start your dates in cell A1 like you stated.

--
Regards,
Tom Ogilvy

right click the excel x just to the left of FILE on the menu>copy/paste
this>save. Now, each time the file is opened excel will check the last cell
in col A of sheet 10. Modify to suit.

Private Sub Workbook_Open()
Set x = Sheets("sheet10").Cells(Rows.Count, "a").End(xlUp).Offset(1)
If x.Offset(-1) <> Date Then x.Value = Date
End Sub

 

[Don Guillett]

a1 is named DATES

 

[cappuccine]

Thanks! Tom's macro works great!

 

[Tom Ogilvy]

You must be answering in the wrong thread.

Either that or your trying to jerk us all around <g>

 

[Tom Ogilvy]

Your welcome.

 

[Don Guillett]

But mine was shorter <boo hoo>

 

[cappuccine]

ok just to restate my next question. I am using this code for the
current date:

Sub placeDate()
Dim rng as Range
set rng = cells(rows.count,"A").End(xlup)
if not isempty(rng) then
' check that it isn't already there
if rng.value = date then exit sub
' it isn't the current date, so place today's
' date in the next cell
set rng = rng.offset(1,0)
end if

rng.Value = Date
End Sub


Now I want to ask the user what value to input into column E of the
same row as the current date. Can I add that easily in now? And how?

 

[cappuccine]

Haha! Sorry, the first one I couldn't get to work. His was just next in
line to try and it worked first try.