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!

 

[JoeMo]

Assuming you only want to fill one cell each time the user inputs a name:

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


name = InputBox("name?")
If name <>"" Then
If Isempty(Cells(1,1)) then
Cells(1, 1).Value= name
Else
Cells(rows.count,1).End(xlup).Offset(1,0).Value = name
End If
End IF
End Sub

 

[My Aswer Is This]

So do you want the entered name entered on your sheet one time or ten times?
Not sure what your question is?

 

[Fiske]

Yep! it works! Thank you very much! ^^

 

[Fiske]

ADVERTISEMENT

So do you want the entered name entered on your sheet one time or ten times?
Not sure what your question is?

Its like what joe assume, fill in a cell each time a user inputs a name.
Sorry that i did not explain my question properly.

 

[JoeMo]

Yep! it works! Thank you very much! ^^

You are welcome - thanks for the reply.

 

[My Aswer Is This]

ADVERTISEMENT

Glad to see you have it working

Its like what joe assume, fill in a cell each time a user inputs a name.
Sorry that i did not explain my question properly.

 

[Rick Rothstein]

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]

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

Actually, I think I like the following version of my one-liner... first, it is one characters shorter , and second, that "mysterious" minus sign in the Offset property is eliminated.

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

 

[Rick Rothstein]

Actually, I think I like the following version of my one-liner... first, it is one characters shorter , and second, that "mysterious" minus sign in the Offset property is eliminated.

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

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