|
Macro - Selecting Next Available Cell
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 -- cappuccine ------------------------------------------------------------------------ cappuccine's Profile: http://www.excelforum.com/member.php...o&userid=28242 View this thread: http://www.excelforum.com/showthread...hreadid=482006 |
Macro - Selecting Next Available Cell
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 *** Sent via Developersdex http://www.developersdex.com *** |
Macro - Selecting Next Available Cell
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 -- Regards, Tom Ogilvy "cappuccine" wrote in message ... 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 -- cappuccine ------------------------------------------------------------------------ cappuccine's Profile: http://www.excelforum.com/member.php...o&userid=28242 View this thread: http://www.excelforum.com/showthread...hreadid=482006 |
Macro - Selecting Next Available Cell
Just some added INFO.
this assumes you don't want to start your dates in cell A1 like you stated. -- Regards, Tom Ogilvy "Don Guillett" wrote in message ... right click the excel x just to the left of FILE on the menucopy/paste thissave. 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 SalesAid Software "cappuccine" wrote in message ... 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 -- cappuccine ------------------------------------------------------------------------ cappuccine's Profile: http://www.excelforum.com/member.php...o&userid=28242 View this thread: http://www.excelforum.com/showthread...hreadid=482006 |
Macro - Selecting Next Available Cell
a1 is named DATES
-- Don Guillett SalesAid Software "Tom Ogilvy" wrote in message ... Just some added INFO. this assumes you don't want to start your dates in cell A1 like you stated. -- Regards, Tom Ogilvy "Don Guillett" wrote in message ... right click the excel x just to the left of FILE on the menucopy/paste thissave. 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 SalesAid Software "cappuccine" wrote in message ... 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 -- cappuccine ------------------------------------------------------------------------ cappuccine's Profile: http://www.excelforum.com/member.php...o&userid=28242 View this thread: http://www.excelforum.com/showthread...hreadid=482006 |
Macro - Selecting Next Available Cell
Thanks! Tom's macro works great! -- cappuccine ------------------------------------------------------------------------ cappuccine's Profile: http://www.excelforum.com/member.php...o&userid=28242 View this thread: http://www.excelforum.com/showthread...hreadid=482006 |
Macro - Selecting Next Available Cell
You must be answering in the wrong thread.
Either that or your trying to jerk us all around <g -- Regards, Tom Ogilvy "Don Guillett" wrote in message ... a1 is named DATES -- Don Guillett SalesAid Software "Tom Ogilvy" wrote in message ... Just some added INFO. this assumes you don't want to start your dates in cell A1 like you stated. -- Regards, Tom Ogilvy "Don Guillett" wrote in message ... right click the excel x just to the left of FILE on the menucopy/paste thissave. 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 SalesAid Software "cappuccine" wrote in message ... 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 -- cappuccine ------------------------------------------------------------------------ cappuccine's Profile: http://www.excelforum.com/member.php...o&userid=28242 View this thread: http://www.excelforum.com/showthread...hreadid=482006 |
Macro - Selecting Next Available Cell
Your welcome.
-- Regards, Tom Ogilvy "cappuccine" wrote in message ... Thanks! Tom's macro works great! -- cappuccine ------------------------------------------------------------------------ cappuccine's Profile: http://www.excelforum.com/member.php...o&userid=28242 View this thread: http://www.excelforum.com/showthread...hreadid=482006 |
Macro - Selecting Next Available Cell
But mine was shorter <boo hoo
-- Don Guillett SalesAid Software "cappuccine" wrote in message ... Thanks! Tom's macro works great! -- cappuccine ------------------------------------------------------------------------ cappuccine's Profile: http://www.excelforum.com/member.php...o&userid=28242 View this thread: http://www.excelforum.com/showthread...hreadid=482006 |
Macro - Selecting Next Available Cell
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 ------------------------------------------------------------------------ cappuccine's Profile: http://www.excelforum.com/member.php...o&userid=28242 View this thread: http://www.excelforum.com/showthread...hreadid=482006 |
Macro - Selecting Next Available Cell
Haha! Sorry, the first one I couldn't get to work. His was just next in line to try and it worked first try. Don Guillett Wrote: But mine was shorter <boo hoo -- Don Guillett SalesAid Software "cappuccine" wrote in message ... Thanks! Tom's macro works great! -- cappuccine ------------------------------------------------------------------------ cappuccine's Profile: http://www.excelforum.com/member.php...o&userid=28242 View this thread: http://www.excelforum.com/showthread...hreadid=482006 -- cappuccine ------------------------------------------------------------------------ cappuccine's Profile: http://www.excelforum.com/member.php...o&userid=28242 View this thread: http://www.excelforum.com/showthread...hreadid=482006 |
| All times are GMT +1. The time now is 03:31 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com