Textbox Data add i

Question

this macro add data in blank active cell but i need data add in last blank row what is correct in given macro

Sub FillDateSenderFrom()
    For i = 1 To 3
        If Controls("TextBox" & i) <> "" Then
            If i = 1 Then
                Cells(Rows.Count, 4).End(xlUp).End(xlToLeft).Offset(0, i - 1).Select
                Range(ActiveCell, ActiveCell.End(xlUp).Offset(1, 0)).Value = Controls("TextBox" & i).Text
            Else
                Cells(Rows.Count, 4).End(xlUp).End(xlToLeft).Offset(0, 1).Select
                Range(ActiveCell, ActiveCell.End(xlUp).Offset(1, 0)).Value = Controls("TextBox" & i).Text
            End If
        End If
    Next i
End Sub

Please learn to indent your code. – SJR Apr 19 '18 at 09:39 — SJR, Apr 19 '18 at 09:39

score 0 · Answer 1 · answered Apr 19 '18 at 09:05

You need to define the last row and to avoid using Select and ActiveCell, Activate- How to avoid using Select in Excel VBA. Some small working example of your code looks like this:

Private Sub CommandButton1_Click()

    i = 1
    Dim myRow As Long

    If Controls("TextBox" & i) <> "" Then
        myRow = lastRow(columnToCheck:=4)
        Cells(myRow, 4) = Controls("TextBox" & i).Text    
    End If
End Sub

Function lastRow(Optional wsName As String, Optional columnToCheck As Long = 1) As Long

    Dim ws As Worksheet

    If wsName = vbNullString Then
        Set ws = ActiveSheet
    Else
        Set ws = Worksheets(wsName)
    End If

    lastRow = ws.Cells(ws.Rows.Count, columnToCheck).End(xlUp).Row

End Function

In general, if you are working on a form, it is a good idea to mention it.

Textbox Data add i

1 Answers1