If Cell full Write Text to Next Cell

Question

How, in VBA with Excel, can you check if a cell has text in it, and write something to the cell below if it does (infinite times)? I've tried to do this:

Private Sub TextBox1_Change()
  Sheet2.Range("A").Value = Sheet1.TextBox1.Value
End Sub

This just seems to change the whole A column. I want the Text Box on Sheet1 to write its text to the first blank cell in 'Column A' on Sheet2.

Answer 1

I want the Text Box on Sheet1 to write its text to the first blank cell in 'Column A' on Sheet2.

If you want to find last non empty cell and then write your value in the next cell, use this code:

Private Sub TextBox1_Change()
   Dim lastrow As Long
   With Sheet2
       lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
       .Range("A" & lastrow + _
            IIf(lastrow = 1 And .Range("A1") = "", 0, 1)).Value _
        = Sheet1.TextBox1.Value
   End With
End Sub

if you want to find first empty cell in column A, (i.e. if you have data in A1, A2, A4, but not in A3, then first empty cell would be A3) use this code:

Private Sub TextBox1_Change()
    Dim emptyRow As Long
    With Sheet2
        emptyRow = IIf( _
           .Range("A1") = "", 1, _
           IIf(.Range("A2") = "", 2, _
               Application.Min(.Cells(1, "A").End(xlDown).Row + 1, Rows.count)))
        .Range("A" & emptyRow).Value = "S"
    End With
End Sub