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: 13
  • 2022-05-21 11_26_38-TestForm_Save - Excel.png
    2022-05-21 11_26_38-TestForm_Save - Excel.png
    29 KB · Views: 14

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Forum statistics

Threads
1,223,239
Messages
6,170,947
Members
452,368
Latest member
jayp2104

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