Update Existing Information on Worksheet

aroig07

New Member
Joined
Feb 26, 2019
Messages
34
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
 

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

Forum statistics

Threads
1,081,617
Messages
5,360,044
Members
400,565
Latest member
Tommy O

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top