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

**DocAElstein** · 05-26-2022, 05:12 PM

Hello nathan1121,
Welcome to ExcelFox

I am slightly puzzled by you explanation, in particular this bit….

Originally Posted by nathan1121

... 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.....

As far as I can tell, the macro makes no attempt to do that. What the macro actually seems to do is , as you further say. ….

Originally Posted by nathan1121

....it creates new rows if you save the form multiple times instead of updating only the row with matching Ticket Number. ....

I am not picking hairs, I am just trying to understand what you want. I may have missed the point due to my ignorance.
It appears to me that you may be asking either of these two

_Q1 … I have a macro that when I run it adds a new row in worksheet “Database”, for all the info from the Review form on worksheet “Ticket Rating”….
… but if the ticket number already exists, then I want it to update the matching row instead..

_Q2 … I have a macro that when I run it adds a new row in worksheet “Database”, for all the info from the Review form on worksheet “Ticket Rating”….
… I want a macro to update the row on sheet "Database" where the number matches the Ticket Number in column E on worksheet “Ticket Rating”..

Originally Posted by nathan1121

... 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? ....

There is not really anything wrong with your code. Your code is not making any attempt to do what I think you may want. Once again, I am not picking hairs, I am just trying to understand what you want

I am not 100% sure what you want, so for now I will assume you want like my suggested _Q1

More fully, this is what I will give you, that is to say the answer to this question.
Question:
The macro adds a new row in worksheet “Database”, for all the info from the Review form on worksheet “Ticket Rating” if the Ticket number from F7 in worksheet “Ticket Rating” is not to be found in column E of worksheet “Database”
That’s good. That stays. That’s what I want.

But I want that if the Ticket number from F7 in worksheet “Ticket Rating” is already to be found in column E of worksheet “Database”, then the corresponding row in worksheet “Database” should be updated.

Answer ( and explained )
Currently the row number in which to put the data in worksheet “Database” is found by this
iRow = database.Range("e" & Application.Rows.Count).End(xlUp).Row + 1
What that does is give you the next free row in worksheet “Database” . That is fine if the Ticket number is a new one.
What we need to do before that is to see if the Ticket number exists, and if it does then the variable iRow needs to be given the row number of the matched Ticket number in worksheet “Database”

A simple way to achieve this is to use the Match function. In simple terms that returns us the position along a range or array, that it finds something. Pseudo like

[Position along] = ( [What I am looking for] , [Inside this range or array], 0 )
( The option 0 just indicates we want an exact match, not the nearest one )

I will use specifically one of the available Match functions which returns us something like an error text if it finds no match. That way I can use it at the same time to test for that error, to see if I do have a match. - an error text returned indicates in effect no match.
If, on the otherhand it does find a match, it returns the position along, which will then be related to the row number I want. ( If I chose my range appropriately, it will be the actual row number I want. )

So:
Change this

Code:

        iRow = database.Range("e" & Application.Rows.Count).End(xlUp).Row + 1

to this

Code:

     iRow = database.Range("e" & Application.Rows.Count).End(xlUp).Row + 1 ' This is the number of the next free row in worksheet "Database"
    Dim MtchRes As Variant ' I need variant as I may have  a number if I find a match,  or an error if i dont find one
     Let MtchRes = Application.Match(frm.Range("f7").Value, database.Range("E1:E" & iRow - 1 & ""), 0)   '  [Position along] = ( [What I am looking for] , [Inside this range or array], [0] )        '  [0] tells it to look for exact match     '  If no match is found an error is returned
        If Not IsError(MtchRes) Then
         Let iRow = MtchRes ' Effectively we are changing the row number we will use  to the row numbr where we found a match  
        Else
         ' we had no match. We don't need to do anything because iRow needs to be the nexzt free row, as it already is
        End If

Alan

**nathan1121** · 05-29-2022, 01:10 PM

Hi Alan,

My apologies for the confusion. You got my concern correctly on Q1 and the code that you shared works perfectly as needed. I appreciate your help. Thanks a mil