Auto populate date VBA code

K

Keyrookie

Is there a code that allows a date to be entered and succesive dates t
populate?

Right now I have a date in B9 and in B10 I have "=B9+1" which is copie
down the column to complete 365 days.

My concern is an inerrant click could delete the formula(s) and disrup
the sequence.

Thanks in advance for the help
 
C

Claus Busch

Hi,

Am Wed, 23 Jan 2013 19:24:31 +0000 schrieb Keyrookie:
Is there a code that allows a date to be entered and succesive dates to
populate?

have you tried Start => Edit => Fill => Fill Series => Columns => Linear
=> Step Value = 1 => Stop value = 12/31/2013


Regards
Claus Busch
 
C

Claus Busch

Hi again,

Am Wed, 23 Jan 2013 20:53:05 +0100 schrieb Claus Busch:
have you tried Start => Edit => Fill => Fill Series => Columns => Linear
=> Step Value = 1 => Stop value = 12/31/2013

sorry, but not Linear. Choose Date


Regards
Claus Busch
 
K

Keyrookie

Claus
I copied this code exactly as you have it and inserted a date bu
nothing happened. I then substituted "Linear" for "Choose Date" an
still nothing. Am I missing something
 
C

Claus Busch

Hi,

Am Thu, 24 Jan 2013 14:48:19 +0000 schrieb Keyrookie:
I copied this code exactly as you have it and inserted a date but
nothing happened. I then substituted "Linear" for "Choose Date" and
still nothing. Am I missing something?

that's no code. That is a step by step guidance to do it in Excel.
Enter the start date e.g. in A1 and follow the steps.


Regards
Claus Busch
 
C

Claus Busch

Hi,

Am Thu, 24 Jan 2013 14:48:19 +0000 schrieb Keyrookie:
I copied this code exactly as you have it and inserted a date but
nothing happened. I then substituted "Linear" for "Choose Date" and
still nothing. Am I missing something?

if you want to do it with VBA then try (modify to suit):

Sub Test()
Dim StopVal As Long
Dim myYear As Integer

myYear = 2013
StopVal = DateSerial(myYear, 12, 31)
[A1] = DateSerial(myYear, 1, 1)
Range("A1").DataSeries Rowcol:=xlColumns, _
Type:=xlChronological, Date:=xlDay, Step:=1, _
Stop:=StopVal, Trend:=False
End Sub


Regards
Claus Busch
 
K

Keyrookie

Claus,

Thank you for your help, however I still can't get it to work. I copie
the VBA code you gave and nothing happened. I then tried modification
with no results.

Since the VBA code shows the Range as A1, I entered 2013-1-1 in A
expecting the column to populate with succesive dates. It did no
happen. I then entered data into column B thinking the code neede
adjacent data to activate, yet nothing happened.

Apparantly I'm missing something so could you please guide me, step b
step, through the process?
Hi,

Am Thu, 24 Jan 2013 14:48:19 +0000 schrieb Keyrookie:
-
I copied this code exactly as you have it and inserted a date but
nothing happened. I then substituted "Linear" for "Choose Date" and
still nothing. Am I missing something?-

if you want to do it with VBA then try (modify to suit):

Sub Test()
Dim StopVal As Long
Dim myYear As Integer

myYear = 2013
StopVal = DateSerial(myYear, 12, 31)
[A1] = DateSerial(myYear, 1, 1)
Range("A1").DataSeries Rowcol:=xlColumns, _
Type:=xlChronological, Date:=xlDay, Step:=1, _
Stop:=StopVal, Trend:=False
End Sub


Regards
Claus Busch
 
C

Claus Busch

Hi,

Am Fri, 25 Jan 2013 14:28:00 +0000 schrieb Keyrookie:
Apparantly I'm missing something so could you please guide me, step by
step, through the process?

in your workbook press Alt+F11. In VBA-Editor choose Insert => Module.
Copy the code into the code module. Back in Excel => View => View
Macros. Choose the macro by name and rund the macro.


Regards
Claus Busch
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top