Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 26

Thread: Cut row to new sheet if text is found within multiple columns
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    New Member
    Join Date
    Jul 2019
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cut row to new sheet if text is found within multiple columns

    I found out the problem.

    The data in columns AE:AM contains various keywords but after the majority of them it contains a semi colon and a number eg Science;13 or Team Building;9.

    When I tested your code with the semi colon and number removed it worked. As soon as I added it back it didn't.

    I looked to attach a sample doc for you to test but I do not have the appropriate permissions. Apologies.

  2. #12
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,972
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Cut row to new sheet if text is found within multiple columns

    Quote Originally Posted by Mike___ View Post
    I looked to attach a sample doc for you to test but I do not have the appropriate permissions. Apologies.
    It isn't a matter of permissions as the forum does not allow attachments. However, you can post small screen shots of your worksheet(s). Follow the 'Look here' link in my signature block below to find out how.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  3. #13
    New Member
    Join Date
    Jul 2019
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cut row to new sheet if text is found within multiple columns

    Keyword Keyword 2 Keyword 3 Keyword 4 Keyword 5 Keyword 6 Keyword 7 Keyword 8 Keyword 9
    Biomedical research;1 Medical genetics;1 R scripting;1 Unix scripting;1 NGS-tools;2 Next-generation sequencing;2 Bioinformatics;3 Genomics;3 Molecular Diagnostics;9
    Cell Culture;1 Chromatography;1 HPLC;1 Lifesciences;1 Biotechnology;2 GLP;3 PCR;3 Molecular Biology;7 Microbiology;9
    DNA electrophoresis;3 DNA sequencing;3 PCR primer design;3 DNA extraction;4 Genotyping;4 PCR;4 Sequencing;4 Western Blotting;5 Genomics;6
    Diagnostics;2 Life Sciences;2 qPCR;2 Biotechnology;3 Cell Culture;3 Research;3 Genetics;4 Biochemistry;5 Molecular Biology;5
    Biotechnology;1 Cell Culture;1 Clinical Research;1 Immunoassays;1 Infectious Diseases;1 Lifesciences;1 Microbiology;1 Molecular Biology;1 PCR;1
    Infectious Diseases;4 Medical Devices;5 Sequencing;7 Pharmaceutical Industry;8 qPCR;9 Genomics;14 Lifesciences;14 Biotechnology;35 Molecular Biology;45
    Biochemistry;1 Durum Wheat;1 MicroRNA Profiling;1 Molecular Biology;1 Next-generation Sequencing;1 Plant Physiology;1 Scientific Writing;1
    DNA sequencing;1 Epigenetics;1 Lifesciences;1 Prostate Cancer;1 qPCR;2 Genomics;5 Research;7 Genetics;16 Molecular Biology;19
    CMV;1 HPV;1 Herpes;1 Instagram;1 NCBI;1 iPhone;1 iPhoto;1 Multiplex PCR;3 PCR;9
    Genomics;1 Lifesciences;1 Lecturing;3 Proteomics;4 RT-PCR;5 Clinical Research;7 PCR;8 Molecular Biology;15 Cell Culture;21
    Blood Bank;1 Hormones;1 Nanoparticles;1 PLGA;1 Serology;1 Laboratory Skills;3 Biochemistry;4 Microbiology;4 Laboratory Medicine;12
    Agriculture;1 Biotechnology;1 GMP;1 Lifesciences;1 Molecular Biology;1 PCR;1 R&D;1 Six Sigma;1 Validation;1
    qPCR;13 Flow Cytometry;16 PCR;27 Cell Biology;46 Biochemistry;48 Molecular Cloning;56 Cell Culture;59 Western Blotting;74 Molecular Biology;93

  4. #14
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,972
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Cut row to new sheet if text is found within multiple columns

    Thanks for the sample data.
    Oops, big mistake on my part, sorry about that. I had two arguments in the Instr function the wrong way around. It should be
    Code:
    If InStr(1, a(i, j), strToFind, 1) > 0 Then
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  5. #15
    New Member
    Join Date
    Jul 2019
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cut row to new sheet if text is found within multiple columns

    Thanks for your help with this - it now copies across perfectly.

    There is one last remaining issue that doesn't quite work as requested. Each time I run a new search it wipes over what has previously been found rather than adding to the sheet 2 results.

    Apologies for taking up your time on this - but it is much appreciated.

  6. #16
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,972
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Cut row to new sheet if text is found within multiple columns

    I had assumed that we could tell the last row used in Sheet2 by looking for the bottom of column A. Apparently that is not the case. Try changing this line.

    Code:
    With ws2
      lr = .Cells.Find(What:="*", After:=.Cells(1, 1), LookIn:=xlValues, SearchOrder:=xlByRows, _
              SearchDirection:=xlPrevious, SearchFormat:=False).Row
    End With
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  7. #17
    New Member
    Join Date
    Jul 2019
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cut row to new sheet if text is found within multiple columns

    Hmmm I am afraid we are back to nothing copying across again?

  8. #18
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,227
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Cut row to new sheet if text is found within multiple columns

    Assuming you specify the unique beginning text, at minimum, for the keyword you want to search for (for example, "Cell C" for "Cell Culture" would be sufficient although you can type more letters of the keyword if you wish), then does this macro work for you...
    Code:
    Sub Copy_Rows_v2() Dim LastRow1 As Long, LastRow2 As Long, strToFind As String LastRow1 = Sheets("Sheet1").Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row LastRow2 = Sheets("Sheet2").Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row strToFind = InputBox("Enter Keyword to be found") With Sheets("Sheet1").Range("AN2:AN" & LastRow1) .Formula = "=MATCH(""" & strToFind & "*"",Sheet1!AE2:AM2,0)" On Error Resume Next Intersect(Sheets("Sheet1").Columns("AE:AM"), .SpecialCells(xlFormulas, xlNumbers).EntireRow).Copy Sheets("Sheet2").Cells(LastRow2 + 1, "A") On Error GoTo 0 End With Sheets("Sheet1").Columns("AN").Clear End Sub
    Last edited by Rick Rothstein; Jul 26th, 2019 at 05:02 AM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  9. #19
    New Member
    Join Date
    Jul 2019
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cut row to new sheet if text is found within multiple columns

    Hi Rick,

    This copies across the results to columns A to I in sheet 2 rather than to AE to AM.
    I need the entire row copied across if keyword is found as there is data in the other columns that needs to move across too.

  10. #20
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,972
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Cut row to new sheet if text is found within multiple columns

    Quote Originally Posted by Mike___ View Post
    Hmmm I am afraid we are back to nothing copying across again?
    The only effect of the change in post 16 is to determine what row to paste the data in Sheet2, not what is copied.

    Are you sure that you still have the changed line from post 14 & didn't revert to the old version of that code line?
    Last edited by Peter_SSs; Jul 26th, 2019 at 07:44 AM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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