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

 

[MikeeRDX]

Private Sub CmdFamt_Click()
Dim lastrow
Dim amount As Integer
lastrow = Sheets("sheet1").Range("y" & Rows.Count).End(xlUp).Row
amount = txtIamt.Text
For currentrow = 13 To lastrow
If Cells(currentrow, 25).Text = amount Then

txtIamt.Text = Cells(currentrow, 25).Value
txtItem.Text = Cells(currentrow, 17)
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)
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
txtIamt.SetFocus
End Sub

 

[MikeeRDX]

I have created command buttons to allow me to retrieve record by item number (txt.Item.Text) or by amount (txt.Iamt.Text). i have separate text boxes for each variable, of course.

 

[JoeMo]

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

This is yet another macro. Here your currentrow is defined within this procedure only. It is not transportable to the CmdUpdate code you posted earlier. The latter code should throw an error b/c currentrow is undefined. Does it error?

 

[MikeeRDX]

It doesn't. It actually retrieve the item for me. I notice that it doesn't pick up whatever the last record is on my database, though.

 

[MikeeRDX]

I take that back...It DOES work if I search the last record by the item number...however, if I do it by the amount of the last item, it doesn't locate it.

 

[JoeMo]

It doesn't. It actually retrieve the item for me. I notice that it doesn't pick up whatever the last record is on my database, though.

I can't see how the code you posted in your initial post could run standalone w/o a run time error.

 

[MikeeRDX]

Yes, you are correct in that it will give me a run time error if I just input some random record and just simply click on the Update command button. Before I use the update button, I would either need to retrieve (by the Find button) or add (Add command) a record and have the information populate onto my Userform. Then, I would perform whatever changes I need (except the item number---that doesn't ever get changed once created). I would finally click on Update to have the changes made on the existing item number in my database.

 

[MikeeRDX]

Before I use the Update button, I would retrieve an already existing record from my database. The only variable that will never be changed is the item number since it is unique for each record.