Search for and Update changes Only

Cheers Leith, I now know for the future.

Thanks XLAdept, very nicely trimmed. Still trying to get it to search for an existing enty and update where relevant.

I want this marco to check if "Staff Number" exists. If exists rewrite row with changes made, if "Staff Number" dont exist = new row

Hi XLAdept,

"Staff Number" is located in colume "B" of certain sheets. All Staff members data is gathered in rows, the staff number is the unique identifier

Hi XLAdept,

I am unable to add an attachment due to mu employments security. So will explain as well as I can.

In my workbook I have 16 sheets. "Sheet1" is my data entry sheet. This sheet works off a varity of validations.

B3 = Name (No Vallidation)
B4 = Staff Number (No Vallidation)
B5 = Role
B6 = Employment

B10 = System i.e red, blue, green, yellow, and so on (These each have there own sheet within the workbook)

B12 - B33 are all vallidations of level of experiance against the Sub System selected in B10.

I have an add button that once clicked sends the data i have entered to the system selected (B10) sheet. e.g if I add my name and staff number, select my role, and employment. Then select the system i.e Red (this will display all the subsystems Red has), then I select my level of experiance of each of those subsystems. I click the add button and all this data is added to Sheet!"Red" in a single row, name in A2,Staff number in B2, Role C2, Employment D2, System E2, 1st Subsystem F2, 2nd Subsystem G2, and so on.

If another staff member enters there details on the same system ("Red"), their data is added to Sheet!"Red" also, but on the next row.

At the moment if I add my details for System "Red" it will add to Sheet!"Red" (as expected), but if I add more details for the same system ("Red") it will add another row as if I was another staff member.


What I want it to do is update (say if I have just increased my experiance on one of "Reds" subsystems. Meaning I will always only have one entry on Sheet!"Red" for each staff member, but it gets updated as experiance increase.

Now what I was thinking was adding a piece of code to the marco I have earlier on in this thread (Sub.Button 2 Click) to look for my staff number Sheet1!B4 and Sheet1!B10 in the selected systems sheet in Sheet1!B10 , and if that combination exist, relace the new input on my row within that systems sheet with the new data, modifying only the cells that have been updated. If the staff and system combination does not exist, add the new row.


If you can help me with this piece of code, I can try and write the rest. Thanks heaps. Rick

Neither, maybe somthing that replaces cells .

So the code would have to look at if the cell has changed excluding colume B, if it has, replace with new data, if not ignor etc

Heres an example of what it is doing now (columes A - K) top 4 rows are added from Sheet1! End goal is to get the single row to update to look like the 5th row below. So the code would have to look at if the cell has changed, if it has, replace with new data, if not ignor.
Rick 1234 Manager Permanent Red Advanced Beginner Beginner Advanced Beginner
Rick 1234 Manager Permanent Red Competent
Rick 1234 Manager Permanent Red
Rick 1234 Manager Permanent Red Novice Expert Expert Expert


Rick 1234 Manager Permanent Red Novice Expert Expert Expert Advanced Beginner

Has anyone got an idea of how I can do this?

Hi XLAdept,

Thanks, but It is still adding a new row as before.

Awesome, thanks XLAdept, I really apprieciate your help with this

Hi XLADept,

This has stoped adding the new row and overwrites the correct row in the sheets.

Now I just need it to replace only what has changed.

Say if Rick 123456 exist in sheet Red and is good in columes F,G, and H, and then he becomes an Expert in colume G, the end result will have F "Good", G"Expert, and H"Good". At he moment it will overight and will only have colume G "Expert"

XLAdept, thanks very much for your help. With the code you provided I was able to fix the rest, see below.

Thanks again, your a star

HTML Code: 

Awesome, thanks again

which is exactly what I want to do upon transfer from my userform to my one sheet in a different workbook. I have written the code for the transfer to the correct columns in my "host" sheet, but i want it to also do the above opperation based upon the most recent entry which I have coded with a timedate stamp.

Rick 1234 Manager Permanent Red Advanced Beginner Beginner Advanced Beginner
Rick 1234 Manager Permanent Red Competent
Rick 1234 Manager Permanent Red
Rick 1234 Manager Permanent Red Novice Expert Expert Expert


Rick 1234 Manager Permanent Red Novice Expert Expert Expert Advanced Beginner

Since all of my data is going to one sheet, I just cant pick out the code from your previous work to make this happen. Im guessing the new code should be located in the "transfer procedure" as id like the "host" to be as read only as possible

Im trying to just updated any previous data with new, or if new data comes in with blanks it does not overwrite the previous data

Please Login or Register  to view this content.

Let me know if there is anything else you need but the "host" data table is simple. just 27 columns and each one has its own controll on the userform. if its a new entry (by the way the Ref for this find/replace thing is column C and the second is column AA) then it goes into the new row, if not it does what i described above.

Thanks