Search code examples
excelvbauserform

If date = today update row, else, create new row

I am working on a userform to record counts at certain times of the day.

enter image description here

I automatically add the date and day to the first two fields. There are different fields for one day of the week compared to the others, hence the if statement:

Private Sub UserForm_Initialize()
If Format(Date, "ddd") <> "Sat" Then
    DateWkd.Value = Format(Date, "mm/dd/yy")
    DayWkd.Value = Format(Date, "ddd")
Else
    DateSat.Value = Format(Date, "mm/dd")
    DaySat.Value = Format(Date, "ddd")
End If
End Sub

Data will be submitted at different times of the day.

How do I find if the last row's day value equals today's day to update the row, or create a new row if the date doesn't match?

Solution

  • Will write out a little bit more as comments aren't great for code.

    In general, you should appropriately qualify references, so in this case with using your userform, you will need to specify the sheet/etc.

    Dim lr as Long, varDay as Long
varDay = 1  'assumes using ColumnA, but you could make this a Find() function if necessary
With Sheets("Data")
    lr = .Cells( .Rows.Count, varDay).End(xlUp).Row
    If DateTextBox.Value <> .Cells(lr,varDay).Value Then 'Not sure if you want just Date (todays' date, no time) or the value in your input for the comparison (gave arbitrary name for textbox example)
        'Do your thing
    End if
End With

    This would be in your command button for entering data, to determine where it would go. If you need to pull data from the sheet on initialize, you would then set textbox.value = .cell references... note that these two situations are not within the same module.