+ Reply to Thread
Results 1 to 10 of 10

VBA code: complex search,copy and paste

  1. #1
    Forum Contributor
    Join Date
    05-01-2013
    Location
    IND
    MS-Off Ver
    Excel 2007
    Posts
    130

    VBA code: complex search,copy and paste

    Hi

    I need to edit my excel worksheet in such a way as shown:

    A1 data is: x/Makhushi?fref=
    A2 data is: w/m/x/jeorge?fref=

    D1 data is: David is going home
    D2 data is: jeorge is fine
    D3 data is: is Makhushi a good boy


    I want B columns as by matching subtext in column A and D
    B1: Makhushi a good boy
    B2: jeorge is fine

    How do I achieve this if I have data up to 500 ROWS ?
    PS: TEXT CAN BE MISSING IN ANY CELL. matching text must be case insensitive.

    Please help.

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,406

    Re: VBA code: complex search,copy and paste

    Here, try this UDF

    Please Login or Register  to view this content.
    use function as:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    05-01-2013
    Location
    IND
    MS-Off Ver
    Excel 2007
    Posts
    130

    Re: VBA code: complex search,copy and paste

    Thank you for your anawer.
    I changed A1 data "x/Makhushi?fref" to "x/Makhushi.sim.1?fref". And I could not get anything. I need the code to work that way as well sir.
    How to declare case sensitivity ?

  4. #4
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,406

    Re: VBA code: complex search,copy and paste

    It's not about case sensivity.
    It's about your string.

    "x/Makhushi.sim.1?fref" is different than "x/Makhushi?fref" and you didn't explain do you want to compare for example:

    Makhushi
    or
    Makhushi.sim.1

  5. #5
    Forum Contributor
    Join Date
    05-01-2013
    Location
    IND
    MS-Off Ver
    Excel 2007
    Posts
    130

    Re: VBA code: complex search,copy and paste

    my mistake apologies.
    I want if Makhushi is found in any way then it takes "is Makhushi a good boy" from D.
    For example:
    Makhushi.iso
    iso.Makhushi/tree.bell
    tree/bell/makushi/train/tree/bell/makushi/train/
    x/Makhushi.sim.1?fref

    Hope I am clear now.

  6. #6
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,406

    Re: VBA code: complex search,copy and paste

    ok now.

    Two things:

    1. Based on upper example separators between words are / . and ?
    Can there be any more separators like =

    2. If you separate words such way then it will return first matchin criteria.
    For example, if in D column you have:
    Please Login or Register  to view this content.
    Then when you separate text: tree/bell/makushi/train/tree/bell/makushi/train/
    Into words and try to match D column it will return: John climb on the tree
    because word tree is first of checking words and there is no any reason why check makushi and not check tree so I must check them all.

    If this is something that you don't want to then you can define one more column with just matching words.
    For example like this:
    Please Login or Register  to view this content.
    That will match criteria word and return text next to it.
    So tree/bell/makushi/train/tree/bell/makushi/train/
    Will return : is Makhushi a good boy
    Because tree is not matching word.

    Is this make sense?
    Last edited by zbor; 08-17-2015 at 11:32 AM.

  7. #7
    Forum Contributor
    Join Date
    05-01-2013
    Location
    IND
    MS-Off Ver
    Excel 2007
    Posts
    130

    Re: VBA code: complex search,copy and paste

    1. separators can be anything. (even makushi can be found as amakushisionara, bbababbamakushisi)
    2. Yes its a great idea. So can I get the result in multiple columns like

    A1: tree/bell/makushi/train/tree/bell/makushi/train/, E1: is Makhushi a good boy, F1: train is on time
    PS: in C1 and D1 I will put the word and sentences respectively.
    This is exactly what I need. Thanks in advance.

  8. #8
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,406

    Re: VBA code: complex search,copy and paste

    Now you need to define 3 ranges by formula:
    • Range to test
    • Range with crucial word
    • Range with result

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Here is a code:

    Please Login or Register  to view this content.
    Attached Files Attached Files

  9. #9
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,406

    Re: VBA code: complex search,copy and paste

    Note: Ranges where you define data can be bigger than data but must be same size

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  10. #10
    Forum Contributor
    Join Date
    05-01-2013
    Location
    IND
    MS-Off Ver
    Excel 2007
    Posts
    130

    Re: VBA code: complex search,copy and paste

    Thank you.

+ 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. Modifying VBA copy and paste code to search down rather than across
    By Stopwatch in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 07-07-2015, 11:45 AM
  2. [SOLVED] Help w/ complex copy paste using VBA
    By rucypher in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-03-2014, 12:42 PM
  3. Replies: 4
    Last Post: 05-16-2014, 06:30 AM
  4. Search, find, copy and paste macro or code.
    By andrew0789 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-10-2014, 03:01 AM
  5. VBA code to search from different sheet > Copy the row > Paste on different sheet.
    By qpywsqp in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-26-2013, 11:13 AM
  6. [SOLVED] Code to search a cell, copy and paste
    By MDEN in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-18-2012, 05:19 AM
  7. VBA-code for search,copy and paste
    By TUNGANA KURMA RAJU in forum Excel General
    Replies: 0
    Last Post: 12-12-2005, 08:45 AM

Tags for this Thread

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