Write values from textboxes in a single row

Question

I have some textboxes and a button, which when clicked writes the values in the textboxes in a row, here's a screenshot:

And here's the code:

Function theLastRow() As Long
    Dim lastRow As Long

    lastRow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row
    theLastRow = lastRow
End Function

Private Sub button1_Click()

    Sheet2.Cells(theLastRow + 1, 5).Value = Comment.Value

    'cant be left empty
    If (name1.Value <> "" And name2.Value <> "" And szsz.Value <> "" And Sum.Value <> "") Then
        Sheet2.Cells(theLastRow + 1, 1).Value = name1.Value
        Sheet2.Cells(theLastRow + 1, 2).Value = name2.Value
        Sheet2.Cells(theLastRow + 1, 3).Value = szsz.Value
        Sheet2.Cells(theLastRow + 1, 4).Value = Sum.Value

    End If 
End Sub

It almost works how it's supposed to, but not exactly:

Name2, szsz and sum always start one row lower, what's the problem?

Because `Sheet2.Cells(theLastRow + 1, 1).Value = name1.Value` added one row XD — findwindow, Oct 15 '15 at 18:33
Also, check out [this thread](http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba) which can give alternative ideas for finding last row. — BruceWayne, Oct 15 '15 at 18:35
@findwindows 2 Yeah I thought that's the problem, but I still don't know how to fix it (besides deleting the +1 where it's not where it's supposed to), is that possible or should I just simply delete the + 1 where I want? I'm new to this so maybe it's trivial, but I still can't see an other way to make it work, but I'm still curious if it's possible. — SzajnIn, Oct 15 '15 at 18:38
See my answer below. Basically, don't use the function so it won't call each time you try to set the value. — findwindow, Oct 15 '15 at 18:39
As FindWindow points out. You are calling theLastRow each time, writing to the sheet, so the next call the last row will be the one you just wrote to. — MatthewD, Oct 15 '15 at 18:41

score 3 · Accepted Answer · edited Oct 15 '15 at 18:51

3

Per my comment above, try this.

Private Sub button1_Click()

Dim LastRow As Long
LastRow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row

Sheet2.Cells(LastRow + 1, 5).Value = Comment.Value

'cant be left empty
If (name1.Value <> "" And name2.Value <> "" And szsz.Value <> "" And Sum.Value <> "") Then
    Sheet2.Cells(LastRow + 1, 1).Value = name1.Value
    Sheet2.Cells(LastRow + 1, 2).Value = name2.Value
    Sheet2.Cells(LastRow + 1, 3).Value = szsz.Value
    Sheet2.Cells(LastRow + 1, 4).Value = Sum.Value

End If


End Sub

edited Oct 15 '15 at 18:51

MatthewD

6,719
5
22
41

answered Oct 15 '15 at 18:36

findwindow

3,133
1
13
30

1

Not sure if OP is looping existing data or just writing data to the worksheet as it is entered and the button is hit.. You might want to leave both versions posted. – MatthewD Oct 15 '15 at 18:43
I have to write the data as it is entered. – SzajnIn Oct 15 '15 at 18:46

Write values from textboxes in a single row

1 Answers1