VBA to check new entry for duplicates and then copy present values

[Fno]

Hi all.

I know that similar problems can be found around the web, but I am new to the whole VBA thing and thus thought it better to ask.

I have a range in a worksheet, A2:A10000, which contains reference numbers (let's called them X). What I need is that when a reference X gets entered into any cell of that range (A2:A10000), a macro will check if that reference X is anywhere else in that range, aka look for duplicates. IF there is a duplicate, I need an option popup box that asks to copy values from that duplicate (Yes or No). If selected Yes, I would like values from columns B and C, but the same row as the duplicate, to be copied to the new entry made.

As an example, let's say that a duplicate is entered in A30 that is also found in A27. What I then need is to copy the value from B27 to B30 and the value from C27 to C30.
I'm fairly sure this can be simply coded, but I haven't found a way to work around the row-column indexing nor the popup box.

[Mumps1]

Can there be more than one duplicate? To use your example, a duplicate is entered in A30 that is also found in A27 and another in A20 and in A15.

[Fno]

More than one duplicate may be found, yes. It does not matter what duplicate is found. Each duplicate just need the same information in columns A, B and C, while the latter columns are to be manually manipulated.
It is a way of reducing error inputs with large data amounts.

[Mumps1]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter your reference numbers and exit the cell.

Please Login or Register  to view this content.

[Fno]

Works like a charm! Thank you!

I have only one thing that I'm slightly curious about. If I delete the new entry I made, nothing special happens, but if I delete entries in columns A-C (the ones copied), it gives run-time error 13 Type Mismatch.
This also happens if I do it to any other row that has not been copied to.

It's not something big, but I'm curious if that can be solved?

[Mumps1]

It looks like you are selecting A-C and then deleting it. Try this version of the macro. If you delete any reference number in column A, the corresponding data in B and C will be deleted. Does this work for you?

Please Login or Register  to view this content.

If not try:

Please Login or Register  to view this content.

[Fno]

Yeah, it does the trick. It still gives problems if selecting A-C and then delete is performed, but I am just guarding against the stupidity of common workers who are to use the document and have no clue about the code I am implementing.
It does take a little while for the popup box to appear, but I guess that's to come with large ranges.

Thank you for the help :)

[Fno]

Oi, the last code you posted bypasses the error!
Awesome, thank you for that!

[Mumps1]

I think that you may have read my response before I revised it. This version eliminates the error message.

Please Login or Register  to view this content.

[Mumps1]

Glad it worked out.

[Fno]

It works like a charm, really wonderful :)

I am just trying to avoid future calls from people getting confused by the appearance of runtime errors ;)


Thank you for the help and +REP