Passing data to next empty row

[tigerdel]

The code below shows no errors but does not post any data when I click the button

Private Sub cmbAddEntry_Click()
Dim LastRow As Long, ws As Worksheet
Set ws = ActiveSheet
LastRow = ws.Range("a" & Rows.Count).End(xlUp).Row + 1
    ws.Range("B" & LastRow) = txtDate.Text
    ws.Range("G" & LastRow) = cmbVAT.value
    ws.Range("H" & LastRow) = txtValue.value
    ws.Range("C" & LastRow) = cmbSuppliers.value
    If cmbColumn.value = "Stock" Then
    ws.Range("I" & LastRow) = txtNett.value
    Else
    If cmbColumn.value = "Motor Expenses" Then
    ws.Range("J" & LastRow) = txtNett.value
    Else
    If cmbColumn.value = "Rent" Then
    ws.Range("K" & LastRow) = txtNett.value
    Else
    If cmbColumn.value = "Utilities" Then
    ws.Range("L" & LastRow) = txtNett.value
    Else
    If cmbColumn.value = "Telecoms" Then
    ws.Range("M" & LastRow) = txtNett.value
    Else
    If cmbColumn.value = "Printing & Stationery" Then
    ws.Range("N" & LastRow) = txtNett.value
    Else
    If cmbColumn.value = "Adverts & Promotions" Then
    ws.Range("O" & LastRow) = txtNett.value
    Else
    If cmbColumn.value = "Insurance" Then
    ws.Range("P" & LastRow) = txtNett.value
    Else
    If cmbColumn.value = "Sundry Expenses" Then
    ws.Range("Q" & LastRow) = txtNett.value
    Else
    If cmbColumn.value = "Private Drawings" Then
    ws.Range("R" & LastRow) = txtNett.value
    Else
    If cmbColumn.value = "Packaging Postage PO Parcel Force" Then
    ws.Range("S" & LastRow) = txtNett.value
    Else
    If cmbColumn.value = "Banking Fees" Then
    ws.Range("T" & LastRow) = txtNett.value
    Else
    If cmbColumn.value = "Loans" Then
    ws.Range("U" & LastRow) = txtNett.value
    Else
    If cmbColumn.value = "Wages" Then
    ws.Range("V" & LastRow) = txtNett.value
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End Sub

I'm sure that one of you experts out there will immediately see the error I have obviously made

Cheers all and HAPPY NEW YEAR

Derek

 

[Fluff]

If you add a message box as shown, what does it say?

LastRow = ws.Range("a" & Rows.Count).End(xlUp).Row + 1
[COLOR=#ff0000]MsgBox LastRow[/COLOR]
    ws.Range("B" & LastRow) = txtDate.Text

 

[Fluff]

You can also simplify all the nested Ifs like

Private Sub cmbAddEntry_Click()
   Dim LastRow As Long, ws As Worksheet
   Set ws = ActiveSheet
   LastRow = ws.Range("a" & Rows.Count).End(xlUp).Row + 1
   MsgBox LastRow
    ws.Range("B" & LastRow) = txtDate.Text
    ws.Range("G" & LastRow) = cmbVAT.Value
    ws.Range("H" & LastRow) = txtValue.Value
    ws.Range("C" & LastRow) = cmbSuppliers.Value
    Select Case cmbColumn.Value
      Case "Stock"
         ws.Range("I" & LastRow) = txtNett.Value
      Case "Motor Expenses"
         ws.Range("J" & LastRow) = txtNett.Value
      Case "Rent"
         ws.Range("K" & LastRow) = txtNett.Value
   End Select
End Sub

 

[Mentor82]

Hi,
I assume that the field you refer in IFs cmbColumn it's a field on form, is that correct? So you have no error because you have no option explicit turned on. VbA treats cmbColumn as a variant declaration because it's neither referred to form object nor declares as a variable. You shoud do twothins:
1. You should refer cmbColumn to the form object everywhere you use that in the code to get the value out of the form an be able to check it using IF's statement as follows: form_name.cmbColumn.value
2. This point is rather to get a rid of mess in the code about that many ifs. Use select case instead:
Select case form.name_cmbColumn.value
case is "insurance"
Case is "banking"
...
Etc
End select

Hope you'll find that useful.
Regards,
Sebastian

 

[tigerdel]

If you add a message box as shown, what does it say?

LastRow = ws.Range("a" & Rows.Count).End(xlUp).Row + 1
[COLOR=#ff0000]MsgBox LastRow[/COLOR]
    ws.Range("B" & LastRow) = txtDate.Text

I added the code you suggested and it gave the number 502
This would be because in column A I have a formula copied down to row 501
I then changed the ws.Range("a" & Rows.Count) to ws.Range("b" & Rows.Count) so it looked at column B for the empty row and it still said 502 [after I deleted the input in row 502]
There is nothing in column B

Thanks

Derek

 

[Fluff]

So you have no error because you have no option explicit turned on. VbA treats cmbColumn as a variant declaration because it's neither referred to form object nor declares as a variable.

As long as the code is in the userform module, then there is nothing wrong with just using cmbColumn.Value, although I would normally use Me.cmbColumn.Value

Also please do not quote entire posts as it just clutters up the thread.

 

[Fluff]

Try using

   LastRow = ws.Range("A:A").Find("*", , xlValues, , xlByRows, xlPrevious, , , False).Row + 1

to find the next row, what does that give?

 

[tigerdel]

Spot on - works perfectly
Thank you so much

Derek

 

[Fluff]

You're welcome & thanks for the feedback

 

[tigerdel]

So now it just overwrites the line added