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.