Loop to the next row

S

srroduin

I have the following code done. The user wants to be able to push the "Flat"
button as many times as needed to add more information. Every time I loop it
I rewrite over my initial data. How do I make it skip down to the next row?
Also I want the user to have to choose yes or no to continue to loop.

Private Sub Flat_Click()
Dim sreturn As String
sreturn = InputBox("Enter start date dd/mm/yyyy 24hr clock")
[A11] = sreturn
sreturn = InputBox("Enter stop date dd/mm/yyyy 24hr clock")
[B11] = sreturn
sreturn = InputBox("Enter TSN to increase")
[C11] = sreturn
sreturn = InputBox("Enter TSN to decrease")
[D11] = sreturn
sreturn = InputBox("Enter MW Amount")
[E11] = sreturn
End Sub
 
J

Jim Thomlinson

Give this a try. It looks for the first unpopulated cell in column A and puts
the values in that row.

Private Sub Flat_Click()
Dim rng As Range

Set rng = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
rng.Value = InputBox("Enter start date dd/mm/yyyy 24hr clock")
rng.Offset(0, 1).Value = InputBox("Enter stop date dd/mm/yyyy 24hr
clock")
rng.Offset(0, 2).Value = InputBox("Enter TSN to increase")
rng.Offset(0, 3).Value = InputBox("Enter TSN to decrease")
rng.Offset(0, 4).Value = InputBox("Enter MW Amount")
Set rng = Nothing
End Sub
 
S

srroduin

Works great! thanks

Jim Thomlinson said:
Give this a try. It looks for the first unpopulated cell in column A and puts
the values in that row.

Private Sub Flat_Click()
Dim rng As Range

Set rng = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
rng.Value = InputBox("Enter start date dd/mm/yyyy 24hr clock")
rng.Offset(0, 1).Value = InputBox("Enter stop date dd/mm/yyyy 24hr
clock")
rng.Offset(0, 2).Value = InputBox("Enter TSN to increase")
rng.Offset(0, 3).Value = InputBox("Enter TSN to decrease")
rng.Offset(0, 4).Value = InputBox("Enter MW Amount")
Set rng = Nothing
End Sub
--
HTH...

Jim Thomlinson


srroduin said:
I have the following code done. The user wants to be able to push the "Flat"
button as many times as needed to add more information. Every time I loop it
I rewrite over my initial data. How do I make it skip down to the next row?
Also I want the user to have to choose yes or no to continue to loop.

Private Sub Flat_Click()
Dim sreturn As String
sreturn = InputBox("Enter start date dd/mm/yyyy 24hr clock")
[A11] = sreturn
sreturn = InputBox("Enter stop date dd/mm/yyyy 24hr clock")
[B11] = sreturn
sreturn = InputBox("Enter TSN to increase")
[C11] = sreturn
sreturn = InputBox("Enter TSN to decrease")
[D11] = sreturn
sreturn = InputBox("Enter MW Amount")
[E11] = sreturn
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top