CODEWITHSUNDEEP
vbaexceluserform
liboan1997
liboan1997

Reputation: 53

Incrementing cell row in excel

I have a UserForm that's going to submit data from the fields to columns A, B, and C, but I need it to move down and fill in the next empty row every time a user hits submit.

This is what I have so far, I don't know what I would put in to make it so it would go from A2/B2/C2 to A3/B3/C3, etc.

Private Sub Submit_Click()
   Dim LastRow As Object

   Set LastRow = Sheet1.Range("a65536").End(xlUp)

   Sheet1.Range("A2").Value = MatchNum.Text
   Sheet1.Range("B2").Value = TeamNum.Text
   Sheet1.Range("C2").Value = AllianceTeamNum.Text

   MsgBox "One record written to Sheet1"

End Sub

I'm a complete beginner at Visual Basic (approx. 1 hr. of experience) and it'd be nice if the solution is as simple as possible. Any help would be much appreciated!

Upvotes: 0

Views: 1361

Answers (2)

user2063626
user2063626

Reputation:

Try below code :

Private Sub Submit_Click()
    With Sheet1
        .Select

        Dim LastRow As Long
        LastRow = .Range("A65536").End(xlUp).Row + 1

        .Range("A" & LastRow).Value = MatchNum.Text
        .Range("B" & LastRow).Value = TeamNum.Text
        .Range("C" & LastRow).Value = AllianceTeamNum.Text

    End With
    MsgBox "One record written to Sheet1"

End Sub

Upvotes: 3

Iswanto San
Iswanto San

Reputation: 18569

Try this:

Dim start As Integer

Private Sub CommandButton1_Click()

   Dim LastRow As Object

   Set LastRow = Sheet1.Range("a65536").End(xlUp)

   Sheet1.Range("A" & start).Value = "a"
   Sheet1.Range("B" & start).Value = "b"
   Sheet1.Range("C" & start).Value = "c"

   MsgBox "One record written to Sheet1"

   start = start + 1

End Sub

Private Sub UserForm_Initialize()
   start = 2
End Sub

Upvotes: -1

Related Questions