Nrthugu

I have a simple form that allows to enter counts at different times of the day.

My form loads the values of the last row in the table to the UserForm so that whoever opens the form to enter the next count gets the previous values placed in the form:

Private Sub UserForm_Initialize()

Dim lr As Long

lr = ActiveSheet.Range("A1").End(xlDown).Row

eightWkd.Value = Cells(lr, 3)

nineWkd.Value = Cells(lr, 4)

ten30Wkd.Value = Cells(lr, 6)

noonWkd.Value = Cells(lr, 8)

one30Wkd.Value = Cells(lr, 10)

threeWkd.Value = Cells(lr, 12)

four30Wkd.Value = Cells(lr, 14)

sixWkd.Value = Cells(lr, 15)

But, each submission creates a new row.

Here is my code for my submit button, setting the values. Changing the loaded values works. I find the first empty row

Private Sub SubmitButton_Click()

Dim ws As Worksheet

Set ws = Worksheets("daily_count")

Dim lr As Long, varDay As Long

With ws

    lr = .Cells(.Rows.Count, varDay).End(xlUp).Offset(1, 0).Row

    .Cells(lr, 1).Value = Me.DateWkd.Value

    .Cells(lr, 2).Value = Me.DayWkd.Value

    .Cells(lr, 3).Value = Me.eightWkd.Value

    .Cells(lr, 4).Value = Me.nineWkd.Value

    .Cells(lr, 6).Value = Me.ten30Wkd.Value

    .Cells(lr, 8).Value = Me.noonWkd.Value

    .Cells(lr, 10).Value = Me.one30Wkd.Value

    .Cells(lr, 12).Value = Me.threeWkd.Value

    .Cells(lr, 14).Value = Me.four30Wkd.Value

    .Cells(lr, 15).Value = Me.sixWkd.Value

I tried the following logic, to choose between the last existing row, and a new row, based on whether the date value (column A) is set, but it didn't work.

If ActiveSheet.Range("A1") = Format(Date, "mm/dd/yy") Then

r = ActiveSheet.Range("A1").End(xlDown).Row

Else

r = .Cells(.Rows.Count, varDay).End(xlUp).Offset(1, 0).Row

End If

So, how do I update cells in the row instead of creating a new row?

How about using the .Find method to find the row with the current date and then update the values of that row if found, and if not found then add the values to the next free row:

Private Sub SubmitButton_Click()

Dim ws As Worksheet

Set ws = Worksheets("daily_count")

Dim lr As Long, varDay As Long, rng as Range

FindVal = Format(Date, "mm/dd/yy")

Set Rng = ws.Range("A:A").Find(What:=FindVal, lookat:=xlWhole)

If Not Rng Is Nothing Then

    With ws

        .Cells(Rng.Row, 1).Value = Me.DateWkd.Value

        .Cells(Rng.Row, 2).Value = Me.DayWkd.Value

        .Cells(Rng.Row, 3).Value = .Cells(Rng.Row, 3).Value + Me.eightWkd.Value

        .Cells(Rng.Row, 4).Value = .Cells(Rng.Row, 4).Value + Me.nineWkd.Value

        .Cells(Rng.Row, 6).Value = .Cells(Rng.Row, 6).Value + Me.ten30Wkd.Value

        .Cells(Rng.Row, 8).Value = .Cells(Rng.Row, 8).Value + Me.noonWkd.Value

        .Cells(Rng.Row, 10).Value = .Cells(Rng.Row, 10).Value + Me.one30Wkd.Value

        .Cells(Rng.Row, 12).Value = .Cells(Rng.Row, 12).Value + Me.threeWkd.Value

        .Cells(Rng.Row, 14).Value = .Cells(Rng.Row, 14).Value + Me.four30Wkd.Value

        .Cells(Rng.Row, 15).Value = .Cells(Rng.Row, 15).Value + Me.sixWkd.Value

    End With

Else

    With ws

        lr = .Cells(.Rows.Count, varDay).End(xlUp).Offset(1, 0).Row

        .Cells(lr, 1).Value = Me.DateWkd.Value

        .Cells(lr, 2).Value = Me.DayWkd.Value

        .Cells(lr, 3).Value = Me.eightWkd.Value

        .Cells(lr, 4).Value = Me.nineWkd.Value

        .Cells(lr, 6).Value = Me.ten30Wkd.Value

        .Cells(lr, 8).Value = Me.noonWkd.Value

        .Cells(lr, 10).Value = Me.one30Wkd.Value

        .Cells(lr, 12).Value = Me.threeWkd.Value

        .Cells(lr, 14).Value = Me.four30Wkd.Value

        .Cells(lr, 15).Value = Me.sixWkd.Value

    End With

End If

End Sub