Help with using VBA "find last row"

A

alliet

New member
Joined
Aug 4, 2013
Messages
5
Reaction score
0
Points
0
Hi, I'm a rookie (started today), in VBA and I've hit a problem when trying to create user a form. I've setup the form and got it to load up but when am struggling with the code that allows for entering data onto a new row and not overriding the data entered first but also how to get it so the data is saved on a different worksheet. I am not sure how to fix the problem. Would appreciate any help anyone can give me. Private Sub btnsave_Click()
Sub LastRowInOneColumn()
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
MsgBox LastRow
Cells(1, 1) = txtassetname.Text
Cells(1, 2) = txtassetid.Text
Cells(1, 3) = txtserialnumber.Text
Cells(1, 4) = txtassetdescription.Text
Cells(1, 5) = txtassetdescription.Text
Cells(1, 6) = txtassetcategory.Text
Cells(1, 7) = txtassettype.Text
Cells(1, 8) = txtsupplier.Text
Cells(1, 9) = txtmanufacturer.Text
Cells(1, 10) = txtmodel.Text
Cells(1, 11) = txtmake.Text
Cells(1, 12) = txtpurchasedate.Text
Cells(1, 13) = txtpurchaseprice.Text
Cells(1, 14) = txtlocation.Text
Cells(1, 15) = txtlocationref.Text
Cells(1, 16) = txtwarrantyexpirydate.Text
 
Your code finds the last row but you don't use it when writing to the sheet.

You don't need the With Statement in this case because you are using the ActiveSheet, but you should not close it until the end of the code that works on that sheet

Code: 
Option Explicit


Sub LastRowInOneColumn()
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
MsgBox LastRow
.Cells(LastRow, 1) = txtassetname.Text
.Cells(LastRow, 2) = txtassetid.Text
'etc
End With
 
Hi Roy Thank you for your assistance with this. I've attached my file but originally I was able to save before I put the code to find the last row and enter the data onto a different worksheet but now the save button doesn't work at all since I added the "last code". I've copied your revised code but I think I have gone wrong and misunderstood. I've attached the file for reference if this helps.Once again than you very mcuh
 

Attachments

  • Asset Register.xlsm
    29.7 KB · Views: 21
Your posted code works with the activesheet and is different to your actual code

Your workbook's code cannot work

Code: 
Option ExplicitPrivate Sub cmdAdd_Click()
    Dim LastRow As Long
    With Sheets("Register")
        LastRow = .Cells(.Ro.Count, "A").End(xlUp).Row + 1


        'copy the data to the database
        .Cells(LastRow, 1).Value = Me.txtassetname.Value
        .Cells(LastRow, 2).Value = Me.txtassetid.Value
        .Cells(LastRow, 3).Value = Me.txtserialnumber.Value
        .Cells(LastRow, 4).Value = Me.txtassetdescription.Value
        .Cells(LastRow, 5).Value = Me.txtassetdescription.Value
        .Cells(LastRow, 6).Value = Me.txtassetcategory.Value
        .Cells(LastRow, 7).Value = Me.txtassettype.Value
        .Cells(LastRow, 8).Value = Me.txtsupplier.Value
        .Cells(LastRow, 9).Value = Me.txtmanufacturer.Value
        .Cells(LastRow, 10).Value = Me.txtmodel.Value
        .Cells(LastRow, 11).Value = Me.txtmake.Value
        .Cells(LastRow, 12).Value = Me.txtpurchasedate.Value
        .Cells(LastRow, 13).Value = Me.txtpurchaseprice.Value
        .Cells(LastRow, 14).Value = Me.txtlocation.Value
        .Cells(LastRow, 15).Value = Me.txtlocationref.Value
        .Cells(LastRow, 16).Value = Me.txtwarrantyexpirydate.Value
    End With
    'clear the data
    With Me
        .txtassetname.Value = ""
        .txtassetid.Value = ""
        .txtserialnumber.Value = ""
        .txtassetdescription.Value = ""
        .txtassetdescription.Value = ""
        .txtassetcategory.Value = ""
        .txtassettype.Value = ""
        .txtsupplier.Value = ""
        .txtmanufacturer.Value = ""
        .txtmodel.Value = ""
        .txtmake.Value = ""
        .txtpurchasedate.Value = ""
        .txtpurchaseprice.Value = ""
        .txtlocation.Value = ""
        .txtlocationref.Value = ""
        .txtwarrantyexpirydate.Value = ""
    End With


End Sub
 
You must log in or register to reply here.
Back
Top