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