Update Row on another sheet based on Cell Value

**nathan1121** · 05-24-2022, 04:20 PM

Hello Everyone,

I have a form on "Ticket Rating" sheet wherein the user enter the number on cell f7. Then I have a button that runs the below macro and it update the row on sheet "Database" where the number matches the Ticket Number in column E, however, it creates new rows if you save the form multiple times instead of updating only the row with matching Ticket Number. Can you tell me what's wrong with my code and how to prevent it from inserting new row when the number already exist in Database sheet? Would really appreciate your help. Thank you
TestForm_Save.xlsm

Code:

Sub Save()
    Dim frm As Worksheet
    Dim database As Worksheet
    Dim dup As Range
    
    Dim iRow As Long
    Dim iSerial As Long
    
    Set frm = ThisWorkbook.Sheets("Ticket Rating")
    Set database = ThisWorkbook.Sheets("Database")
 
  
    If Trim(frm.Range("m1").Value) = "" Then
        iRow = database.Range("e" & Application.Rows.Count).End(xlUp).Row + 1
        
        If iRow = 2 Then
            iSerial = 1
        Else
            iSerial = database.Cells(iRow - 1, 1).Value + 1
        End If
            Else
        iRow = frm.Range("L1").Value
        iSerial = frm.Range("m1").Value
    End If
    
    With database
        .Cells(iRow, 1).Value = iSerial 'ticket
        .Cells(iRow, 5).Value = frm.Range("f7").Value
        .Cells(iRow, 13).Value = frm.Range("n7").Value
        .Cells(iRow, 6).Value = frm.Range("f9").Value
        .Cells(iRow, 18).Value = frm.Range("n9").Value
        .Cells(iRow, 1).Value = frm.Range("N11").Value
        .Cells(iRow, 9).Value = frm.Range("f14").Value
        .Cells(iRow, 11).Value = frm.Range("n14").Value
        .Cells(iRow, 10).Value = frm.Range("h16").Value
        .Cells(iRow, 7).Value = frm.Range("h18").Value
        .Cells(iRow, 14).Value = frm.Range("h21").Value
        .Cells(iRow, 15).Value = frm.Range("h25").Value
        .Cells(iRow, 19).Value = frm.Range("h29").Value
        .Cells(iRow, 20).Value = frm.Range("h35").Value
        .Cells(iRow, 21).Value = frm.Range("h37").Value
    End With
    
    frm.Range("L1").Value = ""
    frm.Range("m1").Value = ""
    
End Sub