Update Existing Information on Worksheet

aroig07

New Member
Joined
Feb 26, 2019
Messages
37
Hi !!

I am stuck on this code, where I am having an issue with the part in red. I already have a code to find the information in each entry with the same exact code but the part after the if/then statement is flipped and it works, but for some reason it does not work to update the entry after changing the desired values when clicking the command button. I also included the code I used to find below on green just in case.

Private Sub CommandButton3_Click()
Dim u As Long
Dim totRows As Long


Set Update = ThisWorkbook.Sheets("Recurrent Job Trail")
totRows = ActiveWorkbook.Worksheets("Recurrent Job Trail").Range("A2").End(xlDown).Row

For u = 2 To totRows
'check if column 1-4 in recurrent job trail matches the job name, client name, account#, and the sub account # in CB
If _
Trim(Sheets("Recurrent Job Trail").Cells(i, 1)) = Trim(ComboBox7.Text) And _
Trim(Sheets("Recurrent Job Trail").Cells(i, 2)) = Trim(ComboBox1.Text) And _
Trim(Sheets("Recurrent Job Trail").Cells(i, 3)) = Trim(ComboBox8.Text) And _
Trim(Sheets("Recurrent Job Trail").Cells(i, 4)) = Trim(ComboBox9.Text) _
Then
Sheets("Recurrent Job Trail").Cells(i, 5).Value = ComboBox2 'update the information for printing machine
Sheets("Recurrent Job Trail").Cells(i, 6).Value = ComboBox3 'update the information for inserting machine
Sheets("Recurrent Job Trail").Cells(i, 7).Value = ComboBox4 'update the information for sorting machine
Sheets("Recurrent Job Trail").Cells(i, 8).Value = TextBox5 'update the information for number of pages
Sheets("Recurrent Job Trail").Cells(i, 9).Value = TextBox6 'update the information for number of pieces
Sheets("Recurrent Job Trail").Cells(i, 10).Value = TextBox7 'update the information for SLA
Sheets("Recurrent Job Trail").Cells(i, 11).Value = "Not Pending" 'update the information for status
Sheets("Recurrent Job Trail").Cells(i, 12).Value = ComboBox5 'update the information for frequency
Sheets("Recurrent Job Trail").Cells(i, 13).Value = ComboBox6 'update the information for exact day it comes depending on frequency
Exit For
End If

Next u


MsgBox "Información ha sido actualizada", vbInformation, "Recurrent Jobs"


ThisWorkbook.Save


Unload AddNewRecurring


End Sub

-------------------Code that works to find the information on the row specific to the characteristics------------------------------------------------


Private Sub CommandButton2_Click()
Dim i As Long
Dim totRows As Long


Set Find = ThisWorkbook.Sheets("Recurrent Job Trail")
totRows = ActiveWorkbook.Worksheets("Recurrent Job Trail").Range("A2").End(xlDown).Row

For i = 2 To totRows
'check if column 1-4 in recurrent job trail matches the job name, client name, account#, and sub account # in CB
If _
Trim(Sheets("Recurrent Job Trail").Cells(i, 1)) = Trim(ComboBox7.Text) And _
Trim(Sheets("Recurrent Job Trail").Cells(i, 2)) = Trim(ComboBox1.Text) And _
Trim(Sheets("Recurrent Job Trail").Cells(i, 3)) = Trim(ComboBox8.Text) And _
Trim(Sheets("Recurrent Job Trail").Cells(i, 4)) = Trim(ComboBox9.Text) _
Then
ComboBox2 = Sheets("Recurrent Job Trail").Cells(i, 5).Value 'find the information for printing machine
ComboBox3 = Sheets("Recurrent Job Trail").Cells(i, 6).Value 'find the information for inserting machine
ComboBox4 = Sheets("Recurrent Job Trail").Cells(i, 7).Value 'find the information for sorting machine
TextBox5 = Sheets("Recurrent Job Trail").Cells(i, 8).Value 'find the information for number of pages
TextBox6 = Sheets("Recurrent Job Trail").Cells(i, 9).Value 'find the information for number of pieces
TextBox7 = Sheets("Recurrent Job Trail").Cells(i, 10).Value 'find the information for SLA
ComboBox5 = Sheets("Recurrent Job Trail").Cells(i, 12).Value 'find the information for frequency
ComboBox6 = Sheets("Recurrent Job Trail").Cells(i, 13).Value 'find the information for exact day it comes depending on frequency
Exit For
End If

Next i

End Sub
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

GR00007

Board Regular
Joined
Apr 22, 2015
Messages
184
When you copied CommandButton2_Click() to CommandButton3_Click() you changed the index "i" to "u", but did not change make the corresponding change of "Cells(i, x)" to "Cells(u, x)"
 

aroig07

New Member
Joined
Feb 26, 2019
Messages
37
How did I miss that -.- I was going crazy. Thank you so much !!! Sometimes a new pair of eyes catches things that are so clear.
 

GR00007

Board Regular
Joined
Apr 22, 2015
Messages
184
Yep - thanx for the reply - BTW: as for going crazy, I think I reached that point decades ago.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,232
Messages
5,594,956
Members
413,954
Latest member
mrsandy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top