Copy and Paste Cells If Match Found

Hi all,

This one is beyond my level but I would very much like to build it into my spreadsheet, so I would appreciate your help.

I have a spreadsheet (range A1:P5000). B2:B5000 would contain cheque numbers. Many of the cheque numbers would be repeated and would have common data in columns C, D, E, F, G, H, L and N.

I am trying to get VB code to copy and paste the common data when a user enters a cheque number.

For example:

When a user enters a cheque number in B3, VB would check B2 for a match. If a match is found, then VB would copy C2, D2, E2, F2, G2, H2, L2 and N2 and paste them in C3, D3, E3, F3, G3, H3, L3 and N3. If no match is found, then the user would have to manually enter the data in C3, D3, E3, F3, G3, H3, L3 and N3.

When a user enters a cheque number in B4, VB would check the B2:B3 for a match. If a match is found (in B2 – for example), then VB would copy C2, D2, E2, F2, G2, H2, L2 and N2 and paste them in C4, D4, E4, F4, G4, H4, L4 and N4. If no match is found, then the user would have to manually enter the data in C4, D4, E4, F4, G4, H4, L4 and N4.

When a user enters a cheque number in B100, VB would check the B2:B99 for a match. If a match is found (in B90 – for example), then VB would copy C90, D90, E90, F90, G90, H90, L90 and N90 and paste them in C100, D100, E100, F100, G100, H100, L100 and N100. If no match is found, then the user would have to manually enter the data in C100, D100, E100, F100, G100, H100, L100 and N100.

Thank you very much,
Gos-C

Paste the following macro into the tab corresponding to the data sheet in the VBA editor (shift F11)

Please Login or Register  to view this content.

mrice, I am getting "Object variable or With block variable not set." Also, can you please code it to use Option Explicit.

Leith, it can be the first match - the data to be copied will be the same for all the matches.

Thank you,
Gos-C

Hello Gos-c,

Copy this macro into a Standard VBA module. You can then add a call to it from any worksheet's Change event procedure.

Please Login or Register  to view this content.

Calling Example
The worksheet will invoke the macro whenever the user types in a cell in column "B". The macro will search from the start row to the cell row above the active cell for a match. If the data match then the specific cells will be copied.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

Great! It works perfectly.

Thank you very much, Leith - greatly appreciated.

Gos-C

Here's a non-looping version.

Please Login or Register  to view this content.

Hi Leith,

After a few more testing, I found a problem.

I entered 123456 as the first cheque number, and when I entered 123456 as the second cheque number the data was copied as expected.
I entered 23456 as the next cheque number, and nothing is copied - as expected, but when I entered 23456 as the fourth cheque number, the data for cheque number 123456 (instead of the data for cheque number 23456) was copied.

Similarly, when I entered 3456 as the next cheque number, nothing is copied - as expected, but when I entered 3456 as the sixth cheque number, the data for cheque number 123456 (instead of the data for cheque number 3456) was copied.

Any fix?

mikerickson, I haven't tried your submission as yet. I will do so immediately after posting this, and then I'll let you know how it worked.

Thanks,
Gos-C

mikerickson, I pasted the code in the sheet module but it's not working.

Gos-C

Is the B entry the only thing in that row? If not, it won't trigger.
If you want this to happen if there is already a column A entry, edit this line:

Please Login or Register  to view this content.

Right on!

I have a formula, =IF(B5<>"",COUNTA($B$2:B5),""), in column A that inserts the row number.

Thank you very much,
Gos-C

If you want to be able to edit existing cheque numbers (column B) without triggering the substitution (and there are no other formulas,etc in the row), you could change that line to

Please Login or Register  to view this content.

As is, the routine is triggered whenever any cell in column B is changed, even when there is existing data in that row.

Thank you, mikerickson. That's good to know.

Gos-C

Hi all,

When I entered the first cheque number (or one that hasn't been previously entered), #N/A is being filled in C, D, E, F, G, H, L, N, and O. I can't figure out how to fix it. Can someone please help me.

BTW, I modified the code as follows:

Please Login or Register  to view this content.

Thank you,
Gos-C

Hello,

I have attached a copy of my workbook. Can anyone tell me how to prevent #N/A from appearing in the cell? I want the cells to be blank, if the cheque number is not found.

Thank you,
Gos-C

Hi everyone,

Any help? I have attached the file.

Thanks,
Gos-C

Gos-C,

Cross posting without the link. Please add the link

VBA Noob

Hi VBA Noob,

Sorry about that. I entered the link to this posting on this forum on the other forum but forgot to do the reverse here.

Here it is:

http://www.mrexcel.com/forum/showthr...=1#post1681237

Gos-C