Update Row on another sheet based on Cell Value

[nathan1121]

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
3912


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


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837)
https://www.eileenslounge.com/viewtopic.php?f=21&t=40701&p=314836#p314836 (https://www.eileenslounge.com/viewtopic.php?f=21&t=40701&p=314836#p314836)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314621#p314621 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314621#p314621)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314619#p314619 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314619#p314619)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314600#p314600 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314600#p314600)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314599#p314599 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314599#p314599)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314274#p314274 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314274#p314274)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314229#p314229 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314229#p314229)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[DocAElstein]

Hello nathan1121,
Welcome to ExcelFox

I am slightly puzzled by you explanation, in particular this bit….
... 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. ….
....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”..


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

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

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]

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 :)

[DocAElstein]

OK, Thx for the feedback
Alan