Trying to use Instr to copy all matches and paste to separate range

Instr Search Copy.xlsm

I am a VBA Novice and so am at an impasse with this code. All help is greatly appreciated! I am trying to use the Instr function to search each populated cell in columns A:H for the value of I1. Lets assume the value of I1 is "Test" and a match is found in row 9 column C, I need columns A:H of row 9 copied to J2:Q2. The code should then continue searching until it reaches the last row of data in range A:H. Each and every match should be copied the same way (Columns A:H) of the row with matching data and be pasted below the other matches starting in J2:Q2. Last, I need for the macro to scan for duplicate matches and remove any found in the range starting in J2:Q2 so that only unique matches are listed under J2:Q2

Thanks!

Hello and thanks for replying!

In my example, Range A:H will be a database of transactions, The code I'm trying to write will search these columns for data which will be input into I1. In my example I have "Test" in I1, and "Test" in C9, The code should find the match in C9 and paste that row, (not the entire row, just columns A:H), in J2
And when I speak of duplicates I am referring to if B9 AND C9 both had "Test" in them, I don't want row 9 showing twice under J2

I hope this clarifies

Instr Search Copy Populated.xlsm

Here I have attached a copy showing Ideal results. You will notice 2 rows have Kohl's referenced and the macro would have found both and copied both rows to J2 and J3 consecutively

Hope this helps!

Originally Posted by skywriter

Read post #2 again.

I'm sorry I don't understand, If you are confused about me naming the same range twice, its because using the Find Function I was able to create something that searched for exact matches but it was pasting every match under J2 so if 1 row had 5 matches it would paste the same row 5 times

You could try a macro like this

Please Login or Register  to view this content.

I have excluded the A column for the serch of the string as this is a column for date. Checking a number og cells for a sting is a slow bussiness so the less columns that needs to be checked the faster the macro will work.

As I'm nor sure of how duplicate results is defined I've let the macro add row number in column R for each match of the string, then values in R column is checked for "duplicate" and range from J to R is cleared of content. Finaly R column is deleted.

Alf

Hello Alf, When I replaced my code with yours i got a debug error

Sorry my bad, I've tested this on your last uploaded file and it seems to work. I've also changed the checking part of the macro to only check column C.
The part checking for duplicate should not be needed in my oppinion so I've removed it.

Please Login or Register  to view this content.

Alf

So strange, I got an error saying "For without Next" and then a debug error on "If InStr(cell, Range("I1")) > 0 Then" line. could you upload the file you are working with?

Hi humdingaling

Thanks for the help.

Alf

unit285,
Try this

Please Login or Register  to view this content.

Thankyou so much Alf, Humdingaling, and Jindon!

Your codes both work! Reputation Added! Thanks!