VBA Code to Update Existing Data in a Sheet

[MikeeRDX]

Hello,

I have a sheet containing data which I have entered. Every now and then I need to update a certain record in a row. I have created the following code to update the data; however, every time I execute the macro, instead of update the existing data it creates a new one on a row row. I don't know how else to get it to work. Whatever help you can provide is greatly appreciated.

Private Sub CmdUpdate_Click()
ActiveSheet.Unprotect Password:="3833"
Dim myitem As String, itemtype As String
Dim center As String, entry As String
Dim issued As String, due As String
Dim amount As String, collection As String
Dim fee As String, source As String
Dim cbbfee As String, institution As String
Dim payor As String, payee As String
Dim instruction As String, paidreturn As String
Dim prdate As String, unpaidreason As String
Dim amtpaid As String, sor As String
Dim Sordate As String, deposit As String
Dim paytype As String, credacct As String, payno As String
myitem = txtItem.Text
Cells(currentrow, 17).Value = myitem
itemtype = txtType.Text
Cells(currentrow, 21).Value = itemtype
center = txtCenter.Text
Cells(currentrow, 31).Value = center
entry = txtEdate.Text
Cells(currentrow, 22).Value = entry
issued = txtIdate.Text
Cells(currentrow, 23).Value = issued
due = txtDdate.Text
Cells(currentrow, 24).Value = due
amount = txtIamt.Text
Cells(currentrow, 25).Value = amount
collection = txtCreason.Text
Cells(currentrow, 38).Value = collection
institution = txtInst.Text
Cells(currentrow, 18).Value = institution
payor = txtPayor.Text
Cells(currentrow, 19).Value = payor
payee = txtPayee.Text
Cells(currentrow, 20).Value = payee
instruction = txtSinstruct.Text
Cells(currentrow, 30).Value = instruction
paidreturn = txtPR.Text
Cells(currentrow, 26).Value = paidreturn
prdate = txtPRdate.Text
Cells(currentrow, 27).Value = prdate
unpaidreason = txtRreason.Text
Cells(currentrow, 34).Value = unpaidreason
amtpaid = txtAmtpd.Text
Cells(currentrow, 28).Value = amtpaid
sor = txtSor.Text
Cells(currentrow, 34).Value = sor
Sordate = txtSorDate.Text
Cells(currentrow, 36).Value = Sordate
deposit = txtSorDep.Text
Cells(currentrow, 37).Value = deposit
paytype = txtPaytype.Text
Cells(currentrow, 29).Value = paytype
credacct = txtAcct.Text
Cells(currentrow, 32).Value = credacct
payno = TxtNo.Text
Cells(currentrow, 33).Value = payno
ActiveSheet.Protect Password:="3833"

End Sub

 

[JoeMo]

How is the variable currentrow defined and initialized?

 

[MikeeRDX]

I have it as a declaration: I apologize I'm new at this but what do you mean by "initialized"? Thank you so much for your assistance.

Dim currentrow As Long
Dim lastrow As Long

 

[JoeMo]

I have it as a declaration: I apologize I'm new at this but what do you mean by "initialized"? Thank you so much for your assistance.

Dim currentrow As Long
Dim lastrow As Long

The code you posted uses the variable currentrow to select cells for updating, but no value has been established for that variable when the code begins using it (e.g. Cells(currentrow, 17).Value = myitem).

 

[MikeeRDX]

Am I missing a series of code? How can I fix this? How do I get the values in the current textboxes to update the data of the "currentrow"? It was able to extract the data from the database; however, I don't know what I need to do to save the updated data over the "currentrow" data?

 

[JoeMo]

Am I missing a series of code? How can I fix this? How do I get the values in the current textboxes to update the data of the "currentrow"? It was able to extract the data from the database; however, I don't know what I need to do to save the updated data over the "currentrow" data?

Is there some additional code you haven't posted? With what you posted, currentrow is either empty (if not dimensioned) or 0 (if dim'd as a Long). Either way, this line:
Cells(currentrow, 17).Value = myitem
should cause a run time error.

 

[MikeeRDX]

Yes. I also have a command button that will enable me to add new record onto the database which calls for it to look for the next empty row and populate it with the new record. Here is the code I have for that command button:

Private Sub CmdAdd_Click()
Dim lastrow As Long
ActiveSheet.Unprotect Password:="3833"
lastrow = Sheets("sheet1").Range("q" & Rows.Count).End(xlUp).Row
Cells(lastrow + 1, "q").Value = txtItem.Text
Cells(lastrow + 1, "r").Value = txtInst.Text
Cells(lastrow + 1, "s").Value = txtPayor.Text
Cells(lastrow + 1, "t").Value = txtPayee.Text
Cells(lastrow + 1, "u").Value = txtType.Text
Cells(lastrow + 1, "v").Value = txtEdate.Text
Cells(lastrow + 1, "w").Value = txtIdate.Text
Cells(lastrow + 1, "x").Value = txtDdate.Text
Cells(lastrow + 1, "y").Value = txtIamt.Text
Cells(lastrow + 1, "z").Value = txtPR.Text
Cells(lastrow + 1, "aa").Value = txtPRdate.Text
Cells(lastrow + 1, "ab").Value = txtAmtpd.Text
Cells(lastrow + 1, "ac").Value = txtPaytype.Text
Cells(lastrow + 1, "ad").Value = txtSinstruct.Text
Cells(lastrow + 1, "ae").Value = txtCenter.Text
Cells(lastrow + 1, "af").Value = txtAcct.Text
Cells(lastrow + 1, "ag").Value = TxtNo.Text
Cells(lastrow + 1, "ah").Value = txtRreason.Text
Cells(lastrow + 1, "ai").Value = txtSor.Text
Cells(lastrow + 1, "aj").Value = txtSorDate.Text
Cells(lastrow + 1, "ak").Value = txtSorDep.Text
Cells(lastrow + 1, "al").Value = txtCreason.Text
Cells(lastrow + 1, "am").Value = txtSource.Text
ActiveSheet.Protect Password:="3833"

 

[JoeMo]

Do you have any other code that uses a variable named currentrow? Maybe I have not been clear, but exactly where your update code places the update values is wholly dependent on the value of currentrow, which is not defined or set anywhere in the update code you posted. What happens when you click the CmdUpdate button?

 

[MikeeRDX]

I have the variable named currentrow in several places but all pointing to the same set of data. Whenever I click on CmdUpdate button, whatever record I have in my textboxes are added to my database as a new record. It should just update an existing record with the same "txt.Item.Text" aka myitem.

 

[MikeeRDX]

Private Sub CmdFind_Click()
Dim lastrow
Dim myitem As String
lastrow = Sheets("sheet1").Range("q" & Rows.Count).End(xlUp).Row
myitem = txtItem.Text
For currentrow = 13 To lastrow
If Cells(currentrow, 17).Text = myitem Then

txtItem.Text = Cells(currentrow, 17).Text
txtInst.Text = Cells(currentrow, 18)
txtPayor.Text = Cells(currentrow, 19)
txtPayee.Text = Cells(currentrow, 20)
txtType.Text = Cells(currentrow, 21)
txtEdate.Text = Cells(currentrow, 22)
txtIdate.Text = Cells(currentrow, 23)
txtDdate.Text = Cells(currentrow, 24)
txtIamt.Text = Cells(currentrow, 25)
txtPR.Text = Cells(currentrow, 26)
txtPRdate.Text = Cells(currentrow, 27)
txtAmtpd.Text = Cells(currentrow, 28)
txtPaytype.Text = Cells(currentrow, 29)
txtSinstruct.Text = Cells(currentrow, 30)
txtCenter.Text = Cells(currentrow, 31)
txtAcct.Text = Cells(currentrow, 32)
TxtNo.Text = Cells(currentrow, 33)
txtRreason.Text = Cells(currentrow, 34)
txtSor.Text = Cells(currentrow, 35)
txtSorDate.Text = Cells(currentrow, 36)
txtSorDep.Text = Cells(currentrow, 37)
txtCreason.Text = Cells(currentrow, 38)
txtSource.Text = Cells(currentrow, 39)

End If
Next currentrow
txtItem.SetFocus
End Sub