Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro
You need to write a Looping macro:
Your variables a BegDate 6/2/2007 DaysApart 6 NumTimesToRepeat 2 Give it a try and write back if you encounter problems. "mustngsalie" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro
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 -- Gary''s Student - gsnu200719 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro
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 "JMay" wrote: You need to write a Looping macro: Your variables a BegDate 6/2/2007 DaysApart 6 NumTimesToRepeat 2 Give it a try and write back if you encounter problems. "mustngsalie" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro
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" wrote: 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 -- Gary''s Student - gsnu200719 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro
Hey, at least we both recognize the lyrics and appreciate good music.
If you have trouble with the MSGBOX's, update the post. -- Gary''s Student - gsnu200719 "mustngsalie" wrote: 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" wrote: 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 -- Gary''s Student - gsnu200719 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro
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" wrote: 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 "JMay" wrote: You need to write a Looping macro: Your variables a BegDate 6/2/2007 DaysApart 6 NumTimesToRepeat 2 Give it a try and write back if you encounter problems. "mustngsalie" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro
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" wrote: 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" wrote: 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 "JMay" wrote: You need to write a Looping macro: Your variables a BegDate 6/2/2007 DaysApart 6 NumTimesToRepeat 2 Give it a try and write back if you encounter problems. "mustngsalie" wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro
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 "mustngsalie" wrote: 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" wrote: 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" wrote: 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 "JMay" wrote: You need to write a Looping macro: Your variables a BegDate 6/2/2007 DaysApart 6 NumTimesToRepeat 2 Give it a try and write back if you encounter problems. "mustngsalie" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
Need syntax for RUNning a Word macro with an argument, called from an Excel macro | Excel Programming | |||
how to count/sum by function/macro to get the number of record to do copy/paste in macro | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |