1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Search and copy Macro in excel.

Discussion in 'Business Applications' started by lukereeve, Feb 13, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. lukereeve

    lukereeve Thread Starter

    Joined:
    Feb 13, 2013
    Messages:
    3
    Hi Guys,

    Would appreciate any help on this issue. I have uploaded an example spreadsheet which outlines the result I am going through. Sheet one will have a splash screen (which hasn't been set up yet other than a table) which will copy a searched result from sheet2 into the table on sheet1.

    I have looked into bombs example from a few years back and recently mentioned on this forum but cannot adapt it to my needs. My experience is quite basic and I haven't done this for a few years. When using bombs previous example I managed to swap it around so that you can search sheet 2 and paste the results into sheet 1 but I had a few issues.

    I was looking for a button where when a user hits it a windows pops up. The user would then type their search term and hit ok. The term they search for would search column G in sheet 2 and pull up all matching rows where the data is found. The only cells that need to be displayed are G, M and N for each respective row and these will be copied into the table on sheet 1. This is outlined in the test spreadsheet uploaded (but the length could be ignored).

    As you can see from the test data sometimes there will be similar (Apple 1, Apple 2) and if a user searched for App or Apple all of the results would need to show. There is no limit to the amount of Apples in the table. The table is not of fixed length and will constantly be added to. Bomb's example would not show the duplicates - although this was intended for his Macro (I am unsure of how to rectify this).

    The second issue with this Macro is I am unsure how to limit the cells it selects and would tend to copy the full row.

    Any guidance would be appreciated!
     

    Attached Files:

  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,576
    First Name:
    Hans
    Hi Luke,
    Welcome to the forum. If I understand you correctly you have some macro's dat already do something but now what you nedd.
    You've attached a non-macro embedded file. Could you please attach the file with the macro's (xlsm) so that I can take a look at the code you're using, no use to reinvent the wheel if all it needs is balancing and maybe a little customizing :)
    I can't promise immediate results but woul like to take a look at it.
     
  3. lukereeve

    lukereeve Thread Starter

    Joined:
    Feb 13, 2013
    Messages:
    3
    Hi,

    Thanks for the response. Here is a very crude version.

    It works for all intents and purposes. It used bombs code - although it searches every column rather than just column G.

    To only select certain cells from the search I have copied the search results into the third shirt and used a formula in the original table which copies this data from sheet 3. When I originally did this every time I hit the search key it would wipe the references for sheet 3 so included in my macro I rewrite these entire formulas every time.

    At least this will now show what I am aiming to achieve and if there is no improvements to the code then so be it! It works.... kind of!

    I can extend the table size on the front page depending on when I put my real data in there but if there was something that could do this on the fly then even better!
     

    Attached Files:

  4. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    Hi

    Here is an updated spreadsheet with the macro altered. Try this out as a copy in case it is not what you need.

    Changes include;

    Searching only Column G
    Eliminating the need for the 3rd sheet by copying only the selected column data to the results sheet
    Making the formatting on the results sheet variable dependant on the number of results

    Give it a try and see if it suits
     

    Attached Files:

  5. lukereeve

    lukereeve Thread Starter

    Joined:
    Feb 13, 2013
    Messages:
    3
    Wow XCubed that is amazing and exactly what i needed! Works perfectly!

    Massive thank you!

    Edit: quick query. My test data only reaches column O but the actually data may have more columns eventually. What areas would I change to column in which the search results are copied down?
     
  6. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    When you add columns to the data sheet remember that column O is reserved for the macro. You would be safer to leave O for that purpose and add new column starting at P.

    If you add columns in the data sheet that you want to include in your results you would add to this set of statements;

    Code:
     
                Sheets(1).Range("B" & Rows.Count).End(xlUp).Offset(1, 0) = Cells(i, 7)
     
     
                Sheets(1).Range("C" & Rows.Count).End(xlUp).Offset(1, 0) = Cells(i, 12)
     
     
                Sheets(1).Range("D" & Rows.Count).End(xlUp).Offset(1, 0) = Cells(i, 13)
    
    the last number is the column number i.e. 7 = G, 12 = L, 13 = M. So f you enter data in column P tht you want to see in your results you would add this line to the above

    Code:
    Sheets(1).Range("E" & Rows.Count).End(xlUp).Offset(1, 0) = Cells(i, 16)
    To get the formatting to work you'd need to manually format the first 2 rows of the new column (header and the first result row) and the macro will pick that up and format the remainder when you run it.

    edit: one other thing you would need to do (that can do now) is change the two lines that say;

    Columns(15).Delete

    to

    Columns(15).ClearContents

    they are on lines 12 an 19 of the macro.
     
  7. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1089372

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice