Sequential Numbering of New Records using VBA

[Denny57]

I have a worksheet which will be used to stored daily medical statistics, updated throughot the day. As the number of rows are known (365) I have a unique point of reference to use when updating records dujring the day.

I have 2 options.

Option 1 is to pre load each row with the calendar day (1st Jan = 1, 2nd Jan = 2 etc... I should then be able to use this as a key to review and update daily information.
Option 2 would be to apply a sequential number to column A of each daily record when initial details are added via the user form using a command button.

I have tried to use option 1 but for some reason the code cannot find a match so I can only assume that as the Day Number was not added through a user form, that the search and data records are incompatible (despite changing the format). The code fails at the "Match" stage

Private Sub cmdViewByDay_Click()
Worksheets("Daily Statistics").Activate
Dim Res As Variant
Dim Lastrow
Dim myFind As String

    Res = Application.Match(txtDayNumber, Sheets("Daily Statistics").Range("A4:A70"), 0)
        If IsError(Res) Then
            MsgBox "Day Number Not Found", vbInformation, "Day Number Not Found"
     Call UserForm_Initialize
     txtDayNumber.SetFocus
    Exit Sub
    End If

   Lastrow = Sheets("Daily Statistics").Range("A" & Rows.Count).End(xlUp).Row
    myFind = txtDayNumber
    For Currentrow = 2 To Lastrow
    If Cells(Currentrow, 1).Text = myFind Then
    txtDayNumber.Value = ActiveSheet.Cells(Currentrow, 1).Value
    txtDate.Value = ActiveSheet.Cells(Currentrow, 2).Value
    txtWeight.Value = ActiveSheet.Cells(Currentrow, 3).Value
    txtBPSystolic = ActiveSheet.Cells(Currentrow, 11).Value
    txtBPDiastolic = ActiveSheet.Cells(Currentrow, 12).Value
    txtPulse = ActiveSheet.Cells(Currentrow, 13).Value
    txtBloodOxygen = ActiveSheet.Cells(Currentrow, 14).Value
    txtMorning = ActiveSheet.Cells(Currentrow, 15).Value
    txtMidday = ActiveSheet.Cells(Currentrow, 16).Value
    txtEvening = ActiveSheet.Cells(Currentrow, 17).Value
        Exit For
    End If
    Next Currentrow
    txtDayNumber.SetFocus

Option 2 would be to add the sequential "Day" number to column A in each row when a new record is added via a Command Button

Current Code to add a new record

Private Sub cmdAddRecord_Click()
Worksheets("Daily Statistics").Activate
Dim Lastrow As Long

Lastrow = Sheets("Daily Statistics").Range("C" & Rows.Count).End(xlUp).Row
Cells(Lastrow + 1, "C").Value = txtWeight
Cells(Lastrow + 1, "K").Value = txtBPSystolic
Cells(Lastrow + 1, "L").Value = txtBPDiastolic
Cells(Lastrow + 1, "M").Value = txtPulse
Cells(Lastrow + 1, "N").Value = txtBloodOxygen
Cells(Lastrow + 1, "O").Value = txtMorning
'Cells(Lastrow + 1, "P").Value = txtMidday
'Cells(Lastrow + 1, "Q").Value = txtEvening
MsgBox "Records have been added to the database", 0, "Records Added"

Call UserForm_Initialize
txtWeight.SetFocus
End Sub

I would be grateful if someone might be able to either resolve the problem why the matching code will not work** or a resolution to add a sequential number to each new record added via the User Form.

** This code works perfectly in several other workbooks

Thanks In Advance

 

[dmt32]

Dave Once again thank you. Your solution works and I have tested a few different scenarios and all were successful..
What baffles me is that I was able to use the same look-up and update code in a number of similar files yet this time neither would work.

There is no secret as to why - although did not say in what way your code did not work, part i guess was due to your ranges not being qualified to workbook / worksheet which, if you have more than one worksheet in your workbook or have more than one workbook open at a time, likely to cause issues which workbook / worksheet code is intended to work with.

A further explanation can be found here: excel-vba Tutorial => Qualifying References

Glad issue resolved & appreciate feedback

Dave