Find first empty cell in column and enter value from textbox

JB's solution is elegant and I'm certain will be that answer that you need, however the one thing I would mention is that it would enter the value in the next empty cell at the bottom of a column of data (ignoring any empty cells interspersed within the data). If you need to find the first empty cell then your piece of code is the one to utilise:

Private Sub TextBox1_Change()
    Range("A1").End(xldown).Offset(1,0).Value = TextBox1.Value
End Sub

The code returned a value in the columns cells below the selected cell.

For example:
c
co
col
colu
colum
column

Private Sub TextBox1_Change()
Range("A2").End(xlDown).Offset(1, 0).value = TextBox1.value
ActiveCell.value = TextBox1.Text
End Sub

Hi Kevin,

That's the "Offset(1, 0).value" it's finding the last filled cell and then selecting the one below that, which is therefore the next empty cell.

Were you looking for a different action?

Yes, I want to write a person's name in the next empty cell in a column.

In this instance I tried getting the word "column" in one cell. Which it did after expending 5 cells above, but I'm asking to fill only one cell with one word. . Some might say I'm too picky, but then I watch my dog search for a place to poop.


VBAcode.png

Hi Kevin,

I see what you're doing there and why it's doing what it is.

You could change the way the input works so that it enters the value in the text box when you click out of it:

Private Sub TextBox1_LostFocus()
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = TextBox1.Text
End Sub

Or you can remove your macro entirely and then add an ActiveX Command Button to the sheet - then right click and select View Code.

Change the code to:

Private Sub CommandButton1_Click()
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = TextBox1.Text
End Sub

Also note that in both examples above you can replace this code:

Range("A2").End(xlDown).Offset(1, 0).value = TextBox1.value
ActiveCell.value = TextBox1.Text

with

Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = TextBox1.Text

These aren't going in the sheet, I'm trying to use them in a userform.

The command button was the best solution. Thank you.

Excellent, glad it's sorted and I've learnt a bit myself in the process

I'm entering data across one row at a time. Not all cells in a row need to be filled for my purposes. Cells B and C will always have a value.

I would like my userform to start writing on the next row down from a cell in B or C.

An option could be that the userform somehow fill in all empty cells in a row with "N/A" before or when it is closed. I could add a command button for that, but what would the code be?


excelprntscr.png

Private Sub CommandButton1_Click()
Unload UserForm1
End Sub
Private Sub CommandButton2_Click()
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = TextBox1.Text
End Sub
Private Sub CommandButton3_Click()
Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = TextBox2.Text
End Sub
Private Sub CommandButton4_Click()
Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Value = TextBox3.Text
End Sub
Private Sub OptionButton1_Click()
Range("d" & Rows.Count).End(xlUp).Offset(1, 0).Value = 1
End Sub
Private Sub OptionButton2_Click()
Range("e" & Rows.Count).End(xlUp).Offset(1, 0).Value = 2
End Sub
Private Sub OptionButton3_Click()
Range("f" & Rows.Count).End(xlUp).Offset(1, 0).Value = 3
End Sub
Private Sub OptionButton4_Click()
Range("g" & Rows.Count).End(xlUp).Offset(1, 0).Value = 4
End Sub