Forum Discussion
VBA Macro to replace data based on matching ID columns
Hi Guys, I am very new to VBA and I have a pretty specific requirement that I could use some help with figuring out.
The code above is used inside of a button in order to open up a different excel spreadsheet and copy the data into my 'Master Spreadsheet'.
With the data that is being copy and pasted into the Master Spreadsheet I also want to be able to check the ID column and if any of the ID's match I want to replace the matching ID row with all of the corresponding ID data from the imported spreadsheet.
All of the data below is dummy data and is not real, however for example, if ID 5 (John Harris) matches ID 5 (Michael Bailey) then I want all of Michael Bailey's data to be replaced with John Harris's data.
I hope that what I have written makes sense and I would appreciate any help with this.
- first off i don't get what/why you are setting your variables that way. in particular why not just assign wb as: set wb=activeworkbook or not at all since you aren't even using wb.
but then I would ask why you are hard coding the copy and master sheets, but maybe this is all just part of the 'sample' and not your real macro.
to answer your question there are a couple of options.
either way you need to do a loop.
A) after you copy the new data over you could loop through all the data and if a match is found then delete the row you don't want. Something like this:
iRow=1
Do While len(wsDest.cells(iRow,1).value2)>1
For jRow = 1 to iRow-1
if wsDest.cells(iRow,1).value2 = wsDest.cells(jRow,1) then
wsDest.cells(jRow,1).entirerow.delete
exit For
end if
next jRow
Loop
B) instead of doing the bulk copy
wsCopy.Range("A2:E" & ...
do a loop (example below) instead and check each value against the existing sheet data and either replace a row or add it to the end.
iRow=1
For iRow = 1 to lCopyLastRow
For jRow = 1 to lDestLastRow
if wsDest.cells(iRow,1).value2 = wsDest.cells(jRow,1) then
exit For
end if
next jRow
wsCopy.Range("A"&iRow&":E"&iRow).copy wsDest.Range("A"&jRow&":E"&jRow)
if jRow>lDestLastRow then lDestLastRow=jRow
next iRow
I didn't actually put this into VBA and create fake data and test so I apologize if there is a bug or two but it should be pretty close.
