CODEWITHSUNDEEP
excelvba
user11268842
user11268842

Reputation:

VBA Adding Records to the Sheet

When I click on the add command button by default it's adding in the third row of the sheet. It should be added to the first available row. Is it something i need to change in the VBA code or the Button Properties?

Private Sub CmdAdd_Click()
    Dim wks As Worksheet
    Dim AddNew As Range
    Set wks = Sheet1
    Set AddNew = wks.Range("AA1000000").End(xlup).Offset(1, 0)
    AddNew.Offset(0, 0).Value = txtcustomerid.Text
    AddNew.Offset(0, 1).Value = txtContract.Text
    AddNew.Offset(0, 2).Value = txtContractLBS.Text
    AddNew.Offset(0, 3).Value = txtContractPrice.Text
    AddNew.Offset(0, 4).Value = txtItem.Text
    AddNew.Offset(0, 5).Value = Txtitemnum.Text
    AddNew.Offset(0, 6).Value = TxtCustomerName.Text
    AddNew.Offset(0, 7).Value = Txtstartdate.Text
    AddNew.Offset(0, 8).Value = Txtenddate.Text
    AddNew.Offset(0, 9).Value = TxtSalesPerson.Text
    AddNew.Offset(0, 10).Value = TxtBroker.Text
    AddNew.Offset(0, 11).Value = TxtTerms.Text
    End Sub

Upvotes: 0

Views: 227

Answers (1)

BrettBlade
BrettBlade

Reputation: 82

Consider using ActiveSheet.UsedRange, see: https://www.thespreadsheetguru.com/blog/2014/7/7/5-different-ways-to-find-the-last-row-or-last-column-using-vba

I suspect your current issue is due presence of formatting data, or lack of data in column AA - that may be a typo in your code?

Upvotes: 1

Related Questions