+ Reply to Thread
Results 1 to 14 of 14

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

  1. #1
    Forum Contributor unit285's Avatar
    Join Date
    10-29-2015
    Location
    TN
    MS-Off Ver
    Office 365
    Posts
    242

    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!

  2. #2
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,526

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

    You have no data in your sample.
    You should include data with your example workbook as well as results expected based on your sample data.

    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
    An example of this would be helpful as you are in the upper part of your explanation saying to find the next match, so it makes me wonder what is a duplicate? Why not just find the first match and leave it at that.
    As a reminder, once your original request has been fulfilled please mark this thread as SOLVED by going to the "Thread Tools" drop down list above your first post and choosing solved.

    If you are happy with my help, then please consider clicking the add reputation button in the lower left hand corner of this post.

  3. #3
    Forum Contributor unit285's Avatar
    Join Date
    10-29-2015
    Location
    TN
    MS-Off Ver
    Office 365
    Posts
    242

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

    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

  4. #4
    Forum Contributor unit285's Avatar
    Join Date
    10-29-2015
    Location
    TN
    MS-Off Ver
    Office 365
    Posts
    242

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

    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!

  5. #5
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,526

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

    Read post #2 again.

  6. #6
    Forum Contributor unit285's Avatar
    Join Date
    10-29-2015
    Location
    TN
    MS-Off Ver
    Office 365
    Posts
    242

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

    Quote Originally Posted by skywriter View Post
    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

  7. #7
    Forum Expert
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,496

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

    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

  8. #8
    Forum Contributor unit285's Avatar
    Join Date
    10-29-2015
    Location
    TN
    MS-Off Ver
    Office 365
    Posts
    242

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

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

  9. #9
    Forum Expert
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,496

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

    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

  10. #10
    Forum Contributor unit285's Avatar
    Join Date
    10-29-2015
    Location
    TN
    MS-Off Ver
    Office 365
    Posts
    242

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

    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?

  11. #11
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,634

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

    the "next" was cut off in post#9

    insert next after "END IF"

    Please Login or Register  to view this content.
    just in case you are not aware
    instr is case sensitive, putting "kohl's" or "KOHL's" will not work
    Last edited by humdingaling; 01-05-2016 at 10:46 PM. Reason: typo
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  12. #12
    Forum Expert
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,496

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

    Hi humdingaling

    Thanks for the help.

    Alf

  13. #13
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    16,941

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

    unit285,
    Try this
    Please Login or Register  to view this content.

  14. #14
    Forum Contributor unit285's Avatar
    Join Date
    10-29-2015
    Location
    TN
    MS-Off Ver
    Office 365
    Posts
    242

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

    Thankyou so much Alf, Humdingaling, and Jindon!

    Your codes both work! Reputation Added! Thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Copy and paste row to worksheet that matches column name
    By Topper73 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-12-2013, 12:29 PM
  2. [SOLVED] copy and paste values to another sheet if value in one column matches condition
    By KK1234 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 11-05-2013, 11:36 AM
  3. copy a range of cells N times and paste in separate sheet and also change a specific colum
    By maramkarthik in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-21-2013, 04:27 AM
  4. VBA code to copy and paste range based on separate cell value
    By Mhofu in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-01-2012, 03:03 PM
  5. Macro to Copy & Paste when worksheet names matches
    By Arinp in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-30-2012, 06:54 PM
  6. Macro for WS/WB comparisons, and copy/paste two cells based on matches
    By adilley in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-13-2011, 10:46 PM
  7. Bug: Global Failure? Copy paste ontop if matches criteria
    By trillium in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-11-2011, 01:30 PM
  8. InStr command finding too many matches
    By SarahPintal in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-28-2010, 03:10 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1