Results 1 to 4 of 4

Thread: Help with using VBA "find last row"

  1. #1

    Help with using VBA "find last row"



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Conjurer royUK's Avatar
    Join Date
    Mar 2011
    Location
    Derbyshire, UK
    Posts
    155
    Articles
    0
    Excel Version
    most versions
    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
    Hope that helps

    Roy

  3. #3
    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
    Attached Files Attached Files

  4. #4
    Conjurer royUK's Avatar
    Join Date
    Mar 2011
    Location
    Derbyshire, UK
    Posts
    155
    Articles
    0
    Excel Version
    most versions
    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
    Hope that helps

    Roy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •