Loop to the next row
 

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

Loop to the next row
 

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" wrote:

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

Loop to the next row
 

Works great! thanks

"Jim Thomlinson" wrote:

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" wrote:

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