Last row is not advancing in worksheet

[bostenson]

Chalk it up to my still juvenile experience when it comes to vba; I am attempting to build a userform which allows me to record various budget across 5 (five) distinct sheets based on the value in a combobox. But the data is not being saved in the next empty row in the sheet. I'm missing something in my last row code, what is it? Thanks in advance.

Private Sub cmdSaveAddBudItem_Click()


'Validation
    If Me.cmbCategory.Value = "" Then
            MsgBox "Please enter a budget item Category.", vbCritical
            Exit Sub
    End If
    
    If Me.txbEnterBudgetItemName.Value = "" Then
            MsgBox "Please enter a Budget Item Name.", vbCritical
            Exit Sub
    End If

    Dim Ch As Worksheet
        Set Ch = ThisWorkbook.Sheets("Checking")
    Dim CC As Worksheet
        Set CC = ThisWorkbook.Sheets("CreditCards")
    Dim Ex As Worksheet
        Set Ex = ThisWorkbook.Sheets("Expenses")
    Dim Li As Worksheet
        Set Li = ThisWorkbook.Sheets("Liabilities")
    Dim Sa As Worksheet
        Set Sa = ThisWorkbook.Sheets("Savings")
    
Dim lr As Long
    
' Checking for Duplicates
    ' Checking
        If Application.WorksheetFunction.CountIf(Ch.Range("A:A"), Me.txbEnterBudgetItemName.Value) > 0 Then
               MsgBox "This Budget Item Name already exists, please enter a new name.", vbOKOnly
               Exit Sub
        End If
            lr = Application.WorksheetFunction.CountA(Ch.Range("A:A"))
        
         'Credit Card
        If Application.WorksheetFunction.CountIf(CC.Range("A:A"), Me.txbEnterBudgetItemName.Value) > 0 Then
               MsgBox "This Budget Item Name already exists, please enter a new name.", vbOKOnly
               Exit Sub
        End If
        lr = Application.WorksheetFunction.CountA(CC.Range("A:A"))
    
        ' Expense
         If Application.WorksheetFunction.CountIf(Ex.Range("A:A"), Me.txbEnterBudgetItemName.Value) > 0 Then
               MsgBox "This Budget Item Name already exists, please enter a new name.", vbOKOnly
               Exit Sub
        End If
        lr = Application.WorksheetFunction.CountA(Ex.Range("A:A"))
    
        ' Liabilities
        If Application.WorksheetFunction.CountIf(Li.Range("A:A"), Me.txbEnterBudgetItemName.Value) > 0 Then
               MsgBox "This Budget Item Name already exists, please enter a new name.", vbOKOnly
               Exit Sub
        End If
        lr = Application.WorksheetFunction.CountA(Li.Range("A:A"))
    
        ' Savings
        If Application.WorksheetFunction.CountIf(Sa.Range("A:A"), Me.txbEnterBudgetItemName.Value) > 0 Then
               MsgBox "This Budget Item Name already exists, please enter a new name.", vbOKOnly
      Exit Sub
       End If
       lr = Application.WorksheetFunction.CountA(Sa.Range("A:A"))
    
' Add Budget Item
    If Me.cmbCategory.Value = "Checking" Then
           Ch.Range("A" & lr + 1).Value = Me.txbEnterBudgetItemName.Value
           Ch.Range("B" & lr + 1).Value = Me.txbBudgetBeginBal.Value
    ElseIf Me.cmbCategory.Value = "Credit Card" Then
           CC.Range("A" & lr + 1).Value = Me.txbEnterBudgetItemName.Value
           CC.Range("B" & lr + 1).Value = Me.txbBudgetBeginBal.Value
    ElseIf Me.cmbCategory.Value = "Expense" Then
           Ex.Range("A" & lr + 1).Value = Me.txbEnterBudgetItemName.Value
           Ex.Range("B" & lr + 1).Value = Me.txbBudgetBeginBal.Value
           Ex.Range("C" & lr + 1).Value = Me.txbBudgetTotalDue.Value
           Ex.Range("D" & lr + 1).Value = Me.cmbTermCode.Value
           Ex.Range("E" & lr + 1).Value = Me.cbxAutomaticWithdrawal.Value
    ElseIf Me.cmbCategory.Value = "Liability" Then
           Li.Range("A" & lr + 1).Value = Me.txbEnterBudgetItemName.Value
           Li.Range("B" & lr + 1).Value = Me.txbBudgetBeginBal.Value
           Li.Range("C" & lr + 1).Value = Me.txbBudgetTotalDue.Value
           Li.Range("D" & lr + 1).Value = Me.cmbTermCode.Value
           Li.Range("E" & lr + 1).Value = Me.cbxAutomaticWithdrawal.Value
    Else
           Me.cmbCategory.Value = "Saving"
           Sa.Range("A" & lr + 1).Value = Me.txbEnterBudgetItemName.Value
           Sa.Range("B" & lr + 1).Value = Me.txbBudgetBeginBal.Value
    End If
    
'Clear Add Budget Item
    Me.cmbCategory.Value = ""
    Me.txbEnterBudgetItemName.Value = ""
    Me.txbBudgetBeginBal.Value = ""
    Me.txbBudgetTotalDue.Value = ""
    Me.cmbTermCode.Value = ""
    Me.cbxAutomaticWithdrawal.Value = False
    
MsgBox "Budget Item has been added.", vbInformation
         
End Sub

[SamT]

Two things:
In your code, lr is always referring to the last sheet it references, in your case, Sa, Savings
CountA is the wrong method, it is not guaranteed to be the actual last used Row.

Since you are using the last used Row value on many sheets, you will either need to use many different LastRow Variables, or recompute it for each sheet

A preferred method is

NextRow = Sheet.Cells(Rows.Count, "Column Letter").End(xlUp).Row + 1
'or
NextRow =CH.Cells(Rows.Count, "A").End(xlUp).Row + 1
'or
NextRow =CH.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row

[SamT]

For your consideration

Enum ColumnNumbers
   ItemName = 1
   BeginBal
   TotalDue
   TermCode
   AutoWithdraw
End Enum
'
'
' Add Budget Item 
Select Case Me.cmbCategory
   Case is = "Checking"
      With Ch.Cells(Rows.Count, "A").End(xlUp).Offset(1).EntireRow
           .Cells(ItemName) = Me.txbEnterBudgetItemName   'String
           .Cells(BeginBal) = CCur(Me.txbBudgetBeginBal)      'Currency
      End With
   Case is = "Credit Card"
      With Cc.Cells(Rows.Count, "A").End(xlUp).Offset(1).EntireRow
           .Cells(ItemName) = Me.txbEnterBudgetItemName
           .Cells(BeginBal) = CCur(Me.txbBudgetBeginBal)
      End With
   Case is = "Expense"
      With Ex.Cells(Rows.Count, "A").End(xlUp).Offset(1).EntireRow
           .Cells(ItemName) = Me.txbEnterBudgetItemName
           .Cells(BeginBal) = CCur(Me.txbBudgetBeginBal)
           .Cells(TotalDue) = CCur(Me.txbBudgetTotalDue)
           .Cells(TermCode) = Me.cmbTermCode                 '? String, Number
           .Cells(AutoWithdraw) = Me.cbxAutomaticWithdrawal  '? String. Currency
      End With
   Case is = "Liability"
      With Li.Cells(Rows.Count, "A").End(xlUp).Offset(1).EntireRow
           .Cells(ItemName) = Me.txbEnterBudgetItemName
           .Cells(BeginBal) = CCur(Me.txbBudgetBeginBal)
           .Cells(TotalDue) = CCur(Me.txbBudgetTotalDue)
           .Cells(TermCode) = Me.cmbTermCode
           .Cells(AutoWithdraw) = Me.cbxAutomaticWithdrawal
   Case is = "Saving"
      With Sa.Cells(Rows.Count, "A").End(xlUp).Offset(1).EntireRow
           .Cells(ItemName) = Me.txbEnterBudgetItemName
           .Cells(BeginBal) = CCur(Me.txbBudgetBeginBal)
      End With
End Select

Note how each "Case" and each "NextRow" assignment are related, and, how the use of an Enum makes each value assignment and each Column Assignment "Self checking" for correctness

[bostenson]

Two things:
In your code, lr is always referring to the last sheet it references, in your case, Sa, Savings
CountA is the wrong method, it is not guaranteed to be the actual last used Row.

Since you are using the last used Row value on many sheets, you will either need to use many different LastRow Variables, or recompute it for each sheet

A preferred method is

NextRow = Sheet.Cells(Rows.Count, "Column Letter").End(xlUp).Row + 1
'or
NextRow =CH.Cells(Rows.Count, "A").End(xlUp).Row + 1
'or
NextRow =CH.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row

Hey SamT,

Thank you for the solution, the following includes the corrections I made:

Dim ChNr As Long
    Dim CCNr As Long
    Dim ExNr As Long
    Dim LiNr As Long
    Dim SaNr As Long
    
    ' Checking for Duplicates
    
        ' Checking
    If Application.WorksheetFunction.CountIf(Ch.Range("A:A"), Me.txbEnterBudgetItemName.Value) > 0 Then
        MsgBox "This Budget Item Name already exists, please enter a new name.", vbOKOnly
        Exit Sub
    End If
        
    ChNr = Sheet7.Cells(Rows.Count, "A").End(xlUp).Row + 1
        
         'Credit Card
    If Application.WorksheetFunction.CountIf(CC.Range("A:A"), Me.txbEnterBudgetItemName.Value) > 0 Then
        MsgBox "This Budget Item Name already exists, please enter a new name.", vbOKOnly
        Exit Sub
    End If
    
    CCNr = Sheet9.Cells(Rows.Count, "A").End(xlUp).Row + 1
    
        ' Expense
    If Application.WorksheetFunction.CountIf(Ex.Range("A:A"), Me.txbEnterBudgetItemName.Value) > 0 Then
        MsgBox "This Budget Item Name already exists, please enter a new name.", vbOKOnly
        Exit Sub
    End If
    
    ExNr = Sheet5.Cells(Rows.Count, "A").End(xlUp).Row + 1
    
        ' Liabilities
    If Application.WorksheetFunction.CountIf(Li.Range("A:A"), Me.txbEnterBudgetItemName.Value) > 0 Then
        MsgBox "This Budget Item Name already exists, please enter a new name.", vbOKOnly
        Exit Sub
    End If
    
    LiNr = Sheet6.Cells(Rows.Count, "A").End(xlUp).Row + 1
    
        ' Savings
    If Application.WorksheetFunction.CountIf(Sa.Range("A:A"), Me.txbEnterBudgetItemName.Value) > 0 Then
        MsgBox "This Budget Item Name already exists, please enter a new name.", vbOKOnly
        Exit Sub
    End If
    
    SaNr = Sheet8.Cells(Rows.Count, "A").End(xlUp).Row + 1
    
    ' Add Budget Item

    If Me.cmbCategory.Value = "Checking" Then
        Ch.Range("A" & ChNr).Value = Me.txbEnterBudgetItemName.Value
        Ch.Range("B" & ChNr).Value = Me.txbBudgetBeginBal.Value
    ElseIf Me.cmbCategory.Value = "Credit Card" Then
        CC.Range("A" & CCNr).Value = Me.txbEnterBudgetItemName.Value
        CC.Range("B" & CCNr).Value = Me.txbBudgetBeginBal.Value
    ElseIf Me.cmbCategory.Value = "Expense" Then
        Ex.Range("A" & ExNr).Value = Me.txbEnterBudgetItemName.Value
        Ex.Range("B" & ExNr).Value = Me.txbBudgetBeginBal.Value
        Ex.Range("C" & ExNr).Value = Me.txbBudgetTotalDue.Value
        Ex.Range("D" & ExNr).Value = Me.cmbTermCode.Value
        Ex.Range("E" & ExNr).Value = Me.cbxAutomaticWithdrawal.Value
    ElseIf Me.cmbCategory.Value = "Liability" Then
        Li.Range("A" & LiNr).Value = Me.txbEnterBudgetItemName.Value
        Li.Range("B" & LiNr).Value = Me.txbBudgetBeginBal.Value
        Li.Range("C" & LiNr).Value = Me.txbBudgetTotalDue.Value
        Li.Range("D" & LiNr).Value = Me.cmbTermCode.Value
        Li.Range("E" & LiNr).Value = Me.cbxAutomaticWithdrawal.Value
    Else
        Me.cmbCategory.Value = "Saving"
        Sa.Range("A" & SaNr).Value = Me.txbEnterBudgetItemName.Value
        Sa.Range("B" & SaNr).Value = Me.txbBudgetBeginBal.Value
    End If

I wanted to use the Enum method you suggested, but I am afraid that my knowledge of VBA is still too basic as I wrote the original code within the form itself, which gave me an "Invalid Inside Procedure" error. Does this mean that I have to write it into a module? And if so, do I still write it as a sub procedure?

[SamT]

Enums are Module level. The Form Code page is a module. Enums go before any other Procedures.