Excel VBA finding last row off cell and add data

Question

I'm making a userform for user to input an item with its quantity and price to the receipt because I want to lock the whole sheet so my employee cannot edit the receipt view. What I'm trying to do is when user already add the first item (row 16) and want to add another item, the item is added into the row below the first item (row 17). I already make the code but the problem is that it doesn't detect the last row of the data so it keeps overwriting the first row which is row 16 and doesn't goes down to row 17.

Can somebody help me with fixing the code?

Private Sub btnSubmit_Click()

Dim lastRow2 As Long, m As Variant, str As String
Dim rng1, cell1 As Range

If tbQty.Value > 0 Then
    lastRow2 = ThisWorkbook.Sheets("sheet1").Range("A16" & Rows.Count).End(xlUp).Row+1
    Sheets("sheet1").Range("A" & lastRow2).Value = tbProduk.Value
    Sheets("sheet1").Range("C" & lastRow2).Value = Val(tbQty.Value)
    Sheets("sheet1").Range("D" & lastRow2).Value = Val(tbPrice.Value)
End If

tbProduk.Value = ""
tbQty.Value = ""
tbPrice.Value = ""
tbProduk.SetFocus

End Sub

score 1 · Answer 1 · answered Dec 15 '19 at 17:40

1

Please replace:

lastRow2 = ThisWorkbook.Sheets("sheet1").Range("A16" & Rows.Count).End(xlUp).Row + 1

with

lastRow2 = ThisWorkbook.Sheets("sheet1").Range("A" & ThisWorkbook.Sheets("sheet1").Rows.Count).End(xlUp).Row + 1

or better:

Dim sh as Worksheet
Set Sh = ThisWorkbook.Sheets("sheet1")
lastRow2 = Sh.Range("A" & Sh.Rows.Count).End(xlUp).Row + 1

answered Dec 15 '19 at 17:40

FaneDuru

38,298
4
19
27

1

Not sure why this was downvoted. Seems fine to me. – JvdV Dec 15 '19 at 17:53
I did not see the down vote but it looks that the person who did that changed his minds... – FaneDuru Dec 15 '19 at 18:00

JvdV · Answer 2 · 2019-12-15T17:57:25.487

Use a With statement, and make sure to have Explicit range references, eg:

Private Sub btnSubmit_Click()

Dim lr As Long

If tbQty.Value > 0 Then
    With ThisWorkbook.Sheets("sheet1")
        lr = .Cells(22, 1).End(xlUp).Row + 1
        .Cells(lr, 1).Value = tbProduk.Value
        .Cells(lr, 3).Value = Val(tbQty.Value)
        .Cells(lr, 4).Value = Val(tbPrice.Value)
    End With
End If

tbProduk.Value = ""
tbQty.Value = ""
tbPrice.Value = ""
tbProduk.SetFocus

End Sub

_{Note1: I suspect you want to have another check to see if lr wont go past row 21?}

_{Note2: If you not set on row 22 as per your screenshot, you might want to swap lr = .Cells(22, 1).End(xlUp).Row + 1 for lr = .Cells(.Rows.Count, 1).End(xlUp).Row + 1}

@JustinJunias, did this answer your question? – JvdV Dec 16 '19 at 12:42 — JvdV, Dec 16 '19 at 12:42

score 0 · Answer 3 · answered Dec 15 '19 at 17:55

0

I would simply loop downward from the header row to find the first blank row. Something like this:

Range("D15").select

do until activecell.value = ""

     activecell.offset(1,0).select

loop

lastrow2 = activecell.row +

answered Dec 15 '19 at 17:55

tc_NYC

192
1
2
11

Looping is possible, but not really a prefered method tbh. Looping through `Range` objects is slowing down your code significantly (less notable in a small range ofcourse). Also, there is zero need to `Select` any `Range` object. – JvdV Dec 15 '19 at 17:59

Excel VBA finding last row off cell and add data

3 Answers3