+ Reply to Thread
Results 1 to 15 of 15

Find using Column headers & Copy Row containing Text

  1. #1
    Registered User
    Join Date
    10-08-2019
    Location
    Sunshine Coast, Australia
    MS-Off Ver
    Office 365 ProPlu
    Posts
    9

    Find using Column headers & Copy Row containing Text

    Hi
    i am new to VBA and have cobbled together code from various sources to perform various tasks at the NFP i work at.
    We collect reams of textual information through a call centre and in the process of sorting through 150 column vs 1000 row data I find the need to change my simple Macros column references regularly for additional fields being added.
    Anyway in the following code Sheet A is the master with all the records hence the row count
    I search in Columns L ,V and DG for a string "DDD"and again "EEE" and post the resultant row in an alternative sheet.

    i would like to reference Column headers (eg named ONE COL, FIVE COL and EIGHT COL) rather than each individual column by a loop
    I would also like to reference the strings "DDD" and "EEE" I am searching as well by a loop so as I can minimize the code

    The following code is an abridged version of what is miles long, to demonstrate. Any assistance would be most welcome

    Please Login or Register  to view this content.

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Find using Column headers & Copy Row containing Text

    It looks like you may be able to do what you want by filtering the data instead of looping. It would be easier to help and test possible solutions if you could attach a copy of your file. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Find using Column headers & Copy Row containing Text

    Hi there,

    It's difficult for me to test this properly without access to your data worksheet, but see if the following code gets you moving in the right direction:

    Please Login or Register  to view this content.
    The highlighted values maybe changed to suit your own requirements.

    NOTE: I've assumed that you want the duplicate rows deleted from the TO worksheet ("B"), but if this is not the case just change:

    Please Login or Register  to view this content.
    to:

    Please Login or Register  to view this content.

    Hope this helps - please let me know how you get on.

    Regards,

    Greg M

  4. #4
    Registered User
    Join Date
    10-08-2019
    Location
    Sunshine Coast, Australia
    MS-Off Ver
    Office 365 ProPlu
    Posts
    9

    Re: Find using Column headers & Copy Row containing Text

    Hi MuMps1 and Greg M,
    Thanks very kindly for your responses . I have attached a file with a working Macro, with some data that does what I need and also a description of what I would like to be able to do.
    Greg I have been trying your code (thanks very much!). It is hitting a road block at the
    Please Login or Register  to view this content.
    line.
    I have also put your code in with the file attached.

    Thanks again
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow

    Hi !

    It seems just with an Excel basics feature you do not ever need a code : try an advanced filter !
    And if really a code is necessary using an advanced filter within the procedure simplifies it and it's fast enough …

  6. #6
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Find using Column headers & Copy Row containing Text

    Hi Again,

    Right! Let's see if we can do any better this time around.

    Take a look at the attached version of your workbook and see if it does what you need. It uses the following code:

    Please Login or Register  to view this content.
    The highlighted values may be altered to suit your requirements.


    As far as adding additional "From" worksheets is concerned, it would probably be best to use a table-based approach - i.e. a table containing the various combinations of worksheet name, search column and text to find. This would allow modified search requirements to be entered within "native" Excel rather than within VBA code.


    Hope this helps - as before, please let me know how you get on.

    Regards,

    Greg M
    Attached Files Attached Files
    Last edited by Greg M; 10-11-2019 at 09:16 AM.

  7. #7
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Find using Column headers & Copy Row containing Text

    Have a look at the attached file. I have added a sheet called "References" which contains the search strings and the headers that you want to search. Update this sheet to suit your needs. I'm not sure if I have interpreted correctly what you want to do but try the macro in Module3.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    10-08-2019
    Location
    Sunshine Coast, Australia
    MS-Off Ver
    Office 365 ProPlu
    Posts
    9

    Re: Find using Column headers & Copy Row containing Text

    Thanks to both of your for your solutions which both work great.

    I need to extract to about 30 sheets searching for different text in each, find and copy, so given what you have both said directly or indirectly about referring to a "Reference" table or sheet within the workbook I am operating on sounds like where I should head so as to possibly not need so many macros I can use one macro and then point it to the reference sheet for what components it needs.

    Should I start a new thread to try and solve this with your Macros or can I continue on here
    Thanks again.

  9. #9
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Find using Column headers & Copy Row containing Text

    Please attach a copy of your file with the additional sheets and necessary search data in this thread. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data.

  10. #10
    Registered User
    Join Date
    10-08-2019
    Location
    Sunshine Coast, Australia
    MS-Off Ver
    Office 365 ProPlu
    Posts
    9

    Re: Find using Column headers & Copy Row containing Text

    Thanks Mumps1 , I have attached a file with what I am looking for and various additions.
    Regards
    Attached Files Attached Files

  11. #11
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Find using Column headers & Copy Row containing Text

    Hi again,

    Many thanks for your private message and also for the Reputation increase - much appreciated!

    See if the table-based approach shown in the attached workbook gets you moving in the right direction. It includes a new "Search Parameters" worksheet which contains several Named Ranges which are used for selecting the parameters to be used for the searches.

    The main difference between this workbook and the one I posted previously is the following routine:

    Please Login or Register  to view this content.

    Hope this helps, but please feel free to ask for any further information you require.

    Best regards,

    Greg M
    Attached Files Attached Files

  12. #12
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Find using Column headers & Copy Row containing Text

    When you say
    I search 8 columns typically on a sheet called ALL and post the resultant text matches to up to 30 target sheets
    do you mean that the same resultant text matches are copied to all 30 sheets or are there different search criteria for each sheet? For example, in your "References" sheet, would the range A2:A6 apply to "EXTRACT", A10:A11 to "EXTRACT1" and A14:A15 "EXTRACT2" ?

  13. #13
    Registered User
    Join Date
    10-08-2019
    Location
    Sunshine Coast, Australia
    MS-Off Ver
    Office 365 ProPlu
    Posts
    9

    Re: Find using Column headers & Copy Row containing Text

    My apologies Mumps1. I did attempt to post a reply a week or so ago and apparently it didn't go through. Thanks for your messsage.

    You are right in what you say. There are different search criteria for each sheet, the strings i am searching for are different. The columns for the search are typically the same but sometimes change based on what I would like to search for.
    Normally there is one source sheet and on rare occasions I have to use the results of say Extract 1 to search and post to Extract 2, where for example a search term is common to a lot of the sheets but gets context in the second sheet.
    e.g for sheets split on the basis of Australia and the various states, Road transport department (which is an extract sheet and also a search term) exists in all states but can be better delineated after the data is initially sorted by State .In the end though i do different searches for up to 30 sheets.

  14. #14
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Find using Column headers & Copy Row containing Text

    We have to start assuming that there is only one source sheet. If the search criteria are different for each sheet, using the "References sheet, which ranges apply to which sheet? Would the range A2:A6 apply to "EXTRACT", A10:A11 to "EXTRACT1" and A14:A15 "EXTRACT2"?

  15. #15
    Registered User
    Join Date
    10-08-2019
    Location
    Sunshine Coast, Australia
    MS-Off Ver
    Office 365 ProPlu
    Posts
    9

    Re: Find using Column headers & Copy Row containing Text

    Hi Greg M

    Thanks for your assistance , would you look at the attached file please

    Regards
    Marc
    Attached Files Attached Files

+ 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. VBA Excel: Copy/Paste a column range to another sheet IF column headers match
    By hwatson86 in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 06-07-2016, 08:05 AM
  2. [SOLVED] Find a particular text in column A and copy the value from next column and transpose
    By rkulasekaran in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-29-2016, 03:08 AM
  3. Find a particular text in column A and copy the value from next column and transpose
    By rkulasekaran in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-26-2016, 05:20 AM
  4. Find a particular text in column A and copy the value from next column and transpose
    By rkulasekaran in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-25-2016, 03:20 PM
  5. Find a particular text in column A and copy the value from next column and transpose
    By rkulasekaran in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-25-2016, 03:20 PM
  6. find in excel replace in word: find/replace text in text boxes and headers
    By dean.rogers in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-10-2012, 12:40 PM
  7. find value,loop through 2 sheets and copy headers
    By SarahPintal in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 01-25-2010, 12:48 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