Need Help on inputing values in a cell VBA

[Fiske]

Hi.
I am doing a code where once the command button is press, in open up an input box and asked to input the name after which will be key in the first cell and if input a second name when pressed the command button, it will automatically fill in the next empty cell of the same column.
However, every time input a name, i would fill in 10 cells rather than just one cell. Here is my code.

Private Sub CommandButton1_Click()
Dim name As String
Dim i As Integer


name = InputBox("name?")
For i = 1 To 10
Cells(i, 1).Value = name
Next

End Sub

Hope i could get help from here as i am still new to VBA.
thank you!

 

[Fiske]

Still a one-liner and 17 characters shorter than the short code above...

Private Sub CommandButton1_Click()
  Cells([MIN(IF(A:A="",ROW(A:A)))], 1) = InputBox("Name?")
End Sub

WOW! thanks for the info!! ^^

 

[My Aswer Is This]

Great information Rick.

For those who might be interested, the button's Click event code can be written using one line of code...

Private Sub CommandButton1_Click()
  Cells(Rows.Count, "A").End(xlUp).Offset(-([A1] <> "")) = InputBox("Name?")
End Sub

 

[Rick Rothstein]

WOW! thanks for the info!! ^^

You are quite welcome... I am glad you found what I posted interesting.

Great information Rick.

Thank you for your kind comment. I notice you quoted my originally posted code. I am wondering, though, did you see the code I posted in Message #10... it is still a one-liner, but more compact.

 

[My Aswer Is This]

I see now your last post. showing it this way:

Private Sub CommandButton1_Click()
  Cells([MIN(IF(A:A="",ROW(A:A)))], 1) = InputBox("Name?")
End Sub

I'll use that next time I have the need. Thanks for all your sharing Rick.