I am very new to VBA and have created a UserForm for adding data to a worksheet by basically cutting, pasting and editing codes that I have found by searching the internet. Therefore, I know that my command button codes are not very consistent. I have gotten to the point that I can add new records to the sheet, find an existing record and populating the data back in the UserForm, however I am now stuck at updating an existing record that I just found.
Here's what I got. The cmdUpdate_Click() area is where I'm in trouble. Instead of updating the info that I just found with cmdFind_Click(), it adds a duplicate entry with the new information. (Note I deleted some code that is working fine for dropdown lists as I didn't think you needed all that info, but if it's necessary I can paste my full code. Also my actual report has over 30 columns, I reduced it down to 4 here)
Dim currentrow As Long Private Sub cmdFind_Click() Dim lastrow Dim myfind As String lastrow = Sheets("Inventory List").Range("A" & Rows.Count).End(xlUp).Row myfind = txtReportNumber.Text For currentrow = 3 To lastrow If Cells(currentrow, 1).Text = myfind Then txtReportNumber.Value = Cells(currentrow, 1).Value cmbCounty.Value = Cells(currentrow, 2).Value cmbClass.Value = Cells(currentrow, 3).Value txDateOff.Value = Cells(currentrow, 4).Value End If Next currentrow txtReportNumber.SetFocus End Sub Private Sub cmdUpdate_Click() Cells(currentrow, 1).Value = txtReportNumber.Value Cells(currentrow, 2).Value = cmbCounty.Value Cells(currentrow, 3).Value = cmbClass.Value Cells(currentrow, 4).Value = txDateOff.Value 'clear the data Me.txtReportNumber.Value = "" Me.cmbCounty.Value = "" Me.cmbClass.Value = "" Me.txDateOff.Value = "" Me.txtReportNumber.SetFocus End Sub Private Sub cmdSave_Click() Dim ws As Worksheet Set ws = Worksheets("Inventory List") 'find first empty row in database iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _ SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1 'copy the data to the database With ws .Cells(iRow, 1).Value = Me.txtReportNumber.Value .Cells(iRow, 2).Value = Me.cmbCounty.Value .Cells(iRow, 3).Value = Me.cmbClass.Value .Cells(iRow, 4).Value = Me.txDateOff.Value End With 'clear the data Me.txtReportNumber.Value = "" Me.cmbCounty.Value = "" Me.cmbClass.Value = "" Me.txDateOff.Value = "" Me.txtReportNumber.SetFocus End Sub