If Cell full Write Text to Next Cell

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.

Answers


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

Need Your Help

Writing better JS

javascript

I'm trying to see how I can write plain javascript that would do the same thing that I can do in jquery.

Phone can't connect to Eclipse on debug mode

android memory file-transfer

I have tried connecting the USB debugger on Samsung Galaxy S2 and Samsung Galaxy Nexus. It still manages to fail on both devices. At the moment I am using Windows XP and I am running out of ideas.