Macro

[mustngsalie]

Hello....I want to enter a start date and an end date, and after entering
this information, increment the dates by a specified amount, (example below
shows, 7 days) onto the start date and record each day a specified amount of
times, (example below shows 2), then paste into a specified column. I am of
course new at VBAs and macros, and would appreciate any help. Below is a
copy of what we would do.
2-Jun-07
2-Jun-07
8-Jun-07
8-Jun-07
14-Jun-07
14-Jun-07
20-Jun-07
20-Jun-07
26-Jun-07
26-Jun-07
2-Jul-07
2-Jul-07
8-Jul-07
8-Jul-07
14-Jul-07
14-Jul-07
20-Jul-07
20-Jul-07
26-Jul-07
26-Jul-07
1-Aug-07
1-Aug-07
7-Aug-07
7-Aug-07
13-Aug-07
13-Aug-07
19-Aug-07
19-Aug-07
25-Aug-07
25-Aug-07
31-Aug-07
31-Aug-07
6-Sep-07
6-Sep-07
12-Sep-07
12-Sep-07

 

[JMay]

You need to write a Looping macro:
Your variables are:

BegDate 6/2/2007
DaysApart 6
NumTimesToRepeat 2

Give it a try and write back if you encounter problems.

 

[Gary''s Student]

This is just as easy to do without a macro, but:

Sub ride_sally_ride()
v1 = Range("A1").Value
v2 = Range("A2").Value
j = 6
For i = 3 To 35 Step 2
Cells(i, 1).Value = v1 + j
Cells(i + 1, 1).Value = v2 + j
j = j + 6
Next
End Sub

will replicate you sample data

 

[mustngsalie]

Thank you so much for your response. I need a little more clarification, as
the only training I have had is self taught. I have set it up to ask the
questions in a message box, which is what I need, but how do I get the data
entered to relate to the users response? Below is what I have written so
far...The first message box asks the user to enter the column and row where
they would like the data stored, How do I get the answer to the next message
box (Beginning date) into their previously entered column and row.

Sub dateincrement()
'Date Increment macro
Dim begdate As String
Dim daysapart As String
Dim numtimestorepeat As String
Dim columntoinputdates As String

columntoinputdates = InputBox("Enter the column and start row to store
dates.")
begdate = InputBox("Enter the Beginning Date.")
daysapart = InputBox("Enter increments between dates.")
numbertimestorepeat = InputBox("Enter the number of times to repeat each
date.")



End Sub

 

[mustngsalie]

Gary"s Student...Loved the play on words with ride sally ride...It does get
me the numbers I listed but I want to be able to enter a different date, a
different increment, a certain location to record the data, and a different
end date each time I run the macro. Will a Message box as from my previous
post be the best method?

 

[Gary''s Student]

Hey, at least we both recognize the lyrics and appreciate good music.

If you have trouble with the MSGBOX's, update the post.

 

[JMay]

Give this a shot - Paste into a Standard Module
You will need to Format the Cells populated as Dates,
either before or after running macro.

HTH,
Jim May

Sub Tester()
Dim BegDate As Date 'examples 6/10/2007
Dim DaysApart As Integer ' 7
Dim NumTimesToRepeat As Integer '3
Dim NumPeriods As Integer '5
Dim CellToStartIn As Range
BegDate = InputBox("Enter the Beginning Date example '00/00/0000'")
DaysApart = InputBox("How many days apart?")
NumTimesToRepeat = InputBox("How many times do you wish to repeat the date?")
Set CellToStartIn = Application.InputBox("Click on a Cell WHere you wish to
begin the procedure", Type:=8)
NumPeriods = InputBox("Enter number of Period to Cover")
Application.Goto CellToStartIn
Ctr = 1
Do Until Ctr > NumPeriods
For i = 1 To NumTimesToRepeat
ActiveCell.Cells(i, 1).Value = BegDate
Next i
ActiveCell.Offset(NumTimesToRepeat, 0).Select
BegDate = BegDate + DaysApart
Ctr = Ctr + 1
Loop
End Sub

 

[mustngsalie]

Mr. May;

You response was extremely helpful, it was exactly what I needed. I
have a couple of questions about the macro if you don't mind. I understand
most of the program except when it gets to:
For i = 1 To NumTimesToRepeat
ActiveCell.Cells(i, 1).Value = BegDate
Next i
ActiveCell.Offset(NumTimesToRepeat, 0).Select
BegDate = BegDate + DaysApart
Ctr = Ctr + 1
What does the "i" tell the computer to do? Is there an online class I
can take to learn this programming language?

 

[JMay]

Glad my macro helped..

When you see the "For" work in code it is the beginning of a "For-Next" Loop.
so, immediately Look downward (the the left the the companion word "Next"
When you see the "Next" then center in on every thing in between because
what we are doing is - repeating these lines of code according to the Count
we assign with the letter "i" << the i is a favorite variable used to
take on the value you provide in the "1 To NumTimesToRepeat" that I used
(whick literally means - in this case 1 to ? (? = NumTimesToRepeat)

The line ActiveCell.Offset(NumTimesToRepeat, 0).Select says,
Change the activecell to 3 rows below the current activecell

BegDate = BegDate + DaysApart << means ADD ? (the number assigned to
Daysapart to the current BegDate)

Ctr = Ctr + 1 << Increase the Ctr by 1 (the outer - Loop.

Hope this helps,

Jim May