Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
From worksheet enter DO-Loop start & end code
Using XP & Excel 2000
Trying to figure out how to enter data in worksheet cell ex: G125 & G126; that could be used by the Macro to establish &/or change the Loop Start & Stopping row. Currently Macro Do-loop & For-next starts at cell A128 & enter data in 5 Col to right of cell A128; then returns to A128 goes down one row to cell A129 and agains enters data in 5 Col to right; continues to loop until MyStop at A131. Macro as currently coded works fine! But I would like to modify Do-Loop start and end code from the worksheet,By entering Start cell address in G125 & End cell address in G126, instead of having to go into Macro and edit start & stop range. For instance might want to enter data for 10/24/03 in only row 129. Macro is used to enter golf scores for 5 players by date. Col A = date Col B = Player 1 Col C = Player 2 Col D = Player 3 Col E = Player 4 Col F = Player 5 Thanks fo any help! Bob Leonard ROW A B C D E F G 125 START 126 END 127 128 10/17/03 129 10/24/03 130 10/31/03 131 11/10/03 132 11/17/03 133 134 Sub ForDoLoopEnterDataRight() Dim Value As String, ows As Range 'Below is starting cell Range("A128").Select 'Below is ending cell MyStop = Range("A131") varAnswer = MsgBox("MyStop is A131; To EDIT Macro", vbOKOnly, "My Stop Area") Do For I = 1 To 5 ActiveCell.Offset(0, 1).Select Set ows = ActiveCell Value = InputBox("Enter Value") ActiveCell.Formula = Value ows.Activate Next I ActiveCell.Offset(0, -5).Range("A1").Select ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.Activate varAnswer = MsgBox(ActiveCell.Value, vbOKOnly, "Date") Loop Until ActiveCell = MyStop End Sub .. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
From worksheet enter DO-Loop start & end code
Assume you will put the starting row in G125 and the stopping row in G126
(5 rather than A5 as an example) Sub ForDoLoopEnterDataRight() Dim Value As String, ows As Range 'Below is starting cell Range("A" & Range("G125").Value).Select 'Below is ending cell set MyStop = Range("A" & Range("G126")) varAnswer = MsgBox("MyStop is " & myStop.Address _ & " ; To EDIT Macro", vbOKOnly, "My Stop Area") Do For I = 1 To 5 ActiveCell.Offset(0, 1).Select Set ows = ActiveCell Value = InputBox("Enter Value") ActiveCell.Formula = Value ows.Activate Next I ActiveCell.Offset(0, -5).Range("A1").Select ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.Activate varAnswer = MsgBox(ActiveCell.Value, vbOKOnly, "Date") Loop Until ActiveCell.Row = MyStop.Row End Sub "Bob Leonard" wrote in message ... Using XP & Excel 2000 Trying to figure out how to enter data in worksheet cell ex: G125 & G126; that could be used by the Macro to establish &/or change the Loop Start & Stopping row. Currently Macro Do-loop & For-next starts at cell A128 & enter data in 5 Col to right of cell A128; then returns to A128 goes down one row to cell A129 and agains enters data in 5 Col to right; continues to loop until MyStop at A131. Macro as currently coded works fine! But I would like to modify Do-Loop start and end code from the worksheet,By entering Start cell address in G125 & End cell address in G126, instead of having to go into Macro and edit start & stop range. For instance might want to enter data for 10/24/03 in only row 129. Macro is used to enter golf scores for 5 players by date. Col A = date Col B = Player 1 Col C = Player 2 Col D = Player 3 Col E = Player 4 Col F = Player 5 Thanks fo any help! Bob Leonard ROW A B C D E F G 125 START 126 END 127 128 10/17/03 129 10/24/03 130 10/31/03 131 11/10/03 132 11/17/03 133 134 Sub ForDoLoopEnterDataRight() Dim Value As String, ows As Range 'Below is starting cell Range("A128").Select 'Below is ending cell MyStop = Range("A131") varAnswer = MsgBox("MyStop is A131; To EDIT Macro", vbOKOnly, "My Stop Area") Do For I = 1 To 5 ActiveCell.Offset(0, 1).Select Set ows = ActiveCell Value = InputBox("Enter Value") ActiveCell.Formula = Value ows.Activate Next I ActiveCell.Offset(0, -5).Range("A1").Select ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.Activate varAnswer = MsgBox(ActiveCell.Value, vbOKOnly, "Date") Loop Until ActiveCell = MyStop End Sub . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop for VBA code? | Excel Worksheet Functions | |||
How to Loop some code | Excel Discussion (Misc queries) | |||
VB for excel, how do I loop through code | Excel Discussion (Misc queries) | |||
VBA Newbie: Help with Do Loop code | Excel Discussion (Misc queries) | |||
Creating a loop from my code | Excel Programming |