+ Reply to Thread
Results 1 to 10 of 10

How do you make a search so that data shows up in respective cells

  1. #1
    Registered User
    Join Date
    10-22-2017
    Location
    Jacksonville, FL
    MS-Off Ver
    365
    Posts
    73

    How do you make a search so that data shows up in respective cells

    I would like to have a search box so that when the name searched for is found the appropriate data is placed in cells C2, C4, C6, F2, F4, F6. I cant figure out how to do this.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor ImranBhatti's Avatar
    Join Date
    03-27-2014
    Location
    Rawalpindi,Pakistan
    MS-Off Ver
    Office 365
    Posts
    1,784

    Re: How do you make a search so that data shows up in respective cells

    Something like this should work.

    Please Login or Register  to view this content.
    Teach me Excel VBA

  3. #3
    Registered User
    Join Date
    10-22-2017
    Location
    Jacksonville, FL
    MS-Off Ver
    365
    Posts
    73

    Re: How do you make a search so that data shows up in respective cells

    Thank you for your help. This macro works perfectly... but only if I enter the text in the sort box and go into Macros and run the macros. How can this macros load upon opening the file and run without having to manually trigger the macros?

  4. #4
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: How do you make a search so that data shows up in respective cells

    The macro in post #2 will overwrite the formulas in the destination cells. Your selection change code need to be modified to check if the search box has been changed, search in column A and return the appropriate rec number (i.e. row -8) to cell F3 which is used by the index/match formulas.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by protonLeah; 08-18-2018 at 05:57 PM.
    Ben Van Johnson

  5. #5
    Valued Forum Contributor ImranBhatti's Avatar
    Join Date
    03-27-2014
    Location
    Rawalpindi,Pakistan
    MS-Off Ver
    Office 365
    Posts
    1,784

    Re: How do you make a search so that data shows up in respective cells

    You can call it from Worksheet_Change event like this

    Please Login or Register  to view this content.

  6. #6
    Valued Forum Contributor ImranBhatti's Avatar
    Join Date
    03-27-2014
    Location
    Rawalpindi,Pakistan
    MS-Off Ver
    Office 365
    Posts
    1,784

    Re: How do you make a search so that data shows up in respective cells

    You can call it from Worksheet_Change event like this

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    10-22-2017
    Location
    Jacksonville, FL
    MS-Off Ver
    365
    Posts
    73

    Re: How do you make a search so that data shows up in respective cells

    Thank you for your response. I know very little about VBA and what I presented is what others figured out for me. I wouldn't know how to correct the problem you presented. However, your initial solution does work.

  8. #8
    Valued Forum Contributor ImranBhatti's Avatar
    Join Date
    03-27-2014
    Location
    Rawalpindi,Pakistan
    MS-Off Ver
    Office 365
    Posts
    1,784

    Re: How do you make a search so that data shows up in respective cells

    Please see the attached I have attached copy of your workbook that has the working code. Type a name in A3 and hit enter.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    10-22-2017
    Location
    Jacksonville, FL
    MS-Off Ver
    365
    Posts
    73

    Re: How do you make a search so that data shows up in respective cells

    Thank you for your help. I keep getting a ScrollBar1 error when I go from sheet 1 to sheet 2 and then back to sheet 1. Also is it possible to enter data directly into F2 that will update &F&9 data.

  10. #10
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: How do you make a search so that data shows up in respective cells

    Sorry, the line:
    ' Me.scrollbar1.Max = 4
    was an experiment I forgot to delete. Change to:
    Please Login or Register  to view this content.
    Last edited by protonLeah; 08-19-2018 at 06:36 PM.

+ 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. search and update data Find ther respective header Name
    By baig123 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-09-2014, 02:05 AM
  2. Make a drop down menu that selects & shows data in a table
    By crabbycakes in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-07-2014, 12:27 AM
  3. Replies: 2
    Last Post: 10-24-2013, 11:01 AM
  4. Replies: 12
    Last Post: 08-21-2011, 01:20 PM
  5. How can I make a graph that shows the data distribution?
    By megashock in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 03-02-2011, 09:12 AM
  6. IDing cells in one worksheet, find them in another, and copying respective data
    By cwatson in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-24-2005, 04:04 PM
  7. [SOLVED] How do I copy data (word) into respective cells when the data bei.
    By awg9tech in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 01-12-2005, 08:06 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