VBA: Find and add data from userform to first empty row in sheet (not working)

[Battin]

Hi guys,

I'm having trouble with a macro. I have a worksheet with lots and lots of macro and they work perfectly together.
However, this code (which I've shortened, because of its length) isn't working properly.

I have a sheet full of data for my reservation program. First names, last names, check-in and check-out date, booking# etc. etc. etc.
People can add new bookings to this system and when they do, this code should search the first empty row in column B and add this data to that row.

However, this code seems to look for the LAST row with data and change the data with the new data from the UserForm.

Private Sub btn_save_Click()
        loadingScreen.Show vbModeless
        loadingScreen.Repaint

     Dim EmptyRow
        EmptyRow = WorksheetFunction.CountA(Range("B:B")) + 1
        Dim ws As Worksheet
        Set ws = Worksheets("Guestlist")

    With ws
        'booking# en reservation data
        .Cells(EmptyRow, 2).Value = Me.txtBookingnr.Value
        .Cells(EmptyRow, 3).Value = Me.txtFirstName.Value
        .Cells(EmptyRow, 4).Value = Me.txtLastName.Value
        .Cells(EmptyRow, 5).Value = Format(Me.txtCheckin.Value, "MM-DD-YYYY")
        .Cells(EmptyRow, 6).Value = Format(Me.txtCheckout.Value, "MM-DD-YYYY")

         + PLUS MORE TEXTBOXES

    End with

+ MORE OTHER MACRO CALLS.

    Unload
    loadingScreen
End Sub

I've check if there might be a problem with the other macro's that this one calls, but there isn't. The other macro's doesn't write anything, just do some checks before actually writing the data to the sheet.




The following code is for updating existing data and works perfectly. But uses the offset function (which doesn't really makes a difference i think)

Private Sub btn_update_Click()
        loadingScreen.Show vbModeless
        loadingScreen.Repaint

Dim oRange As Range
             Set oRange = ThisWorkbook.Sheets("Guestlist").Columns(2).Find _
                (What:=txtBookingnr.Value, LookIn:=xlValues, LookAt:=xlWhole)
        oRange.Offset(0, 1).Value = Me.txtFirstName.Value
        oRange.Offset(0, 2).Value = Me.txtLastName.Value
        oRange.Offset(0, 3).Value = Format(Me.txtCheckin.Value, "MM-DD-YYYY")
        oRange.Offset(0, 4).Value = Format(Me.txtCheckout.Value, "MM-DD-YYYY")
[LEFT][COLOR=#222222][FONT=Verdana]        
        + PLUS MORE TEXT BOXES[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]

+ MORE OTHER MACRO CALLS.[/FONT][/COLOR]

[COLOR=#222222][FONT=Verdana]    Unload [/FONT][/COLOR][/LEFT]
loadingScreen
End Sub

I don't get it.

 

[dmt32]

try this update to your code

Private Sub btn_save_Click()
        Dim EmptyRow As Long
        Dim ws As Worksheet
        
        Set ws = Worksheets("Guestlist")
        
        EmptyRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row + 1


    With ws
        'booking# en reservation data
        .Cells(EmptyRow, 2).Value = Me.txtBookingnr.Value
        .Cells(EmptyRow, 3).Value = Me.txtFirstName.Value
        .Cells(EmptyRow, 4).Value = Me.txtLastName.Value
        .Cells(EmptyRow, 5).Value = Format(Me.txtCheckin.Value, "MM-DD-YYYY")
        .Cells(EmptyRow, 6).Value = Format(Me.txtCheckout.Value, "MM-DD-YYYY")


        ' + PLUS MORE TEXTBOXES


    End With


'+ MORE OTHER MACRO CALLS.


    Unload
    loadingScreen
End Sub

Dave

 

[Battin]

Thanks Dave.

I had to add an extra B to the code, otherwise im getting a error message.
Instead of this:

[LEFT][COLOR=#222222][FONT=Verdana]EmptyRow = ws.Cells(ws.Rows.Count, "[B]B[/B]").End(xlUp).Row + 1[/FONT][/COLOR][/LEFT]

I have added an extra B.

EmptyRow = ws.Cells(ws.Rows.Count, "[B]B:B[/B]").End(xlUp).Row + 1

Now it works perfectly.



Thanks you.

 

[dmt32]

strange,
that is a stock bit of code & as published, works ok for me - but no worries so long as have solution to your issue.

Dave

 

[Battin]

I don't get it either, but hé, it worked