When a record is found and amendment done, instead of update with new data a new line of data is created.
Can't find any error in the following macro
Password is 'skb5153'
Sub Store_update_Data()
' OriginalClubAccount
' Takes data from one worksheet and stores in the next empty row on another worksheet.
'or, if the record exists already, it will be updated
Dim sourceSheet As Worksheet
Dim dataSheet As Worksheet
Dim searchValue As Variant
Dim dataVoucherNo As Range
Dim recordRow As Integer
Dim Find As Range
Dim C As Long, i As Long
'Make some sheet variables so we can use those instead of hard-coding sheet references in the code
Set sourceSheet = Sheets("Form")
Set dataSheet = Sheets("TB")
'Column that contains the value for which we will search.
Set dataVoucherNo = dataSheet.Range("E7:E65000")
'Value to search for.
searchValue = sourceSheet.Range("F13").Value
'Check if the user input a value and hit the OK button.
If searchValue <> vbNullString Then
'Value input, so search for it.
'Search
'More specific info:https://docs.microsoft.com/en-us/office/vba/api/excel.range.find
Set Fnd = dataVoucherNo.Find(what:=searchValue, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
MatchCase:=False)
'Check if a record was found.
If Not Fnd Is Nothing Then
'Record Found!
'Get the row of the record.
recordRow = Fnd.Row
recordAddress = Fnd.Address
'Put the records information back into the form.
For C = 5 To 19 Step 2
i = i + 1
sourceSheet.Range(Format(C, "F" & 0)).Value = dataSheet.Cells(recordRow, i).Value
Next C
Else
'Input new record!
'Get the next empty row from the Data Sheet
nextRow = dataSheet.Range("E" & dataSheet.Rows.Count).End(xlUp).Offset(1).Row
End If
'Input the form values into the Data Sheet.
dataSheet.Cells(nextRow, 1).Value = sourceSheet.Range("F5").Value
dataSheet.Cells(nextRow, 2).Value = sourceSheet.Range("F7").Value
dataSheet.Cells(nextRow, 3).Value = sourceSheet.Range("F9").Value
dataSheet.Cells(nextRow, 4).Value = sourceSheet.Range("F11").Value
dataSheet.Cells(nextRow, 5).Value = sourceSheet.Range("F13").Value
dataSheet.Cells(nextRow, 6).Value = sourceSheet.Range("F15").Value
dataSheet.Cells(nextRow, 7).Value = sourceSheet.Range("F17").Value
dataSheet.Cells(nextRow, 8).Value = sourceSheet.Range("F19").Value
'Clear Data
sourceSheet.Range("F5").Value = ""
sourceSheet.Range("F7").Value = ""
sourceSheet.Range("F9").Value = ""
sourceSheet.Range("F11").Value = ""
sourceSheet.Range("F13").Value = ""
sourceSheet.Range("F15").Value = ""
sourceSheet.Range("F17").Value = ""
sourceSheet.Range("F19").Value = ""
End If
End Sub