Update Row on another sheet based on Cell Value

jeremiah_j2k

New Member
Joined
Oct 16, 2014
Messages
32
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



VBA 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
 

Attachments

  • 2022-05-21 11_26_11-TestForm_Save - Excel.png
    2022-05-21 11_26_11-TestForm_Save - Excel.png
    51.5 KB · Views: 14
  • 2022-05-21 11_26_38-TestForm_Save - Excel.png
    2022-05-21 11_26_38-TestForm_Save - Excel.png
    29 KB · Views: 15

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top