From worksheet enter DO-Loop start & end code

[Bob Leonard]

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

..

 

[Tom Ogilvy]

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