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
.
|