+ Reply to Thread
Results 1 to 13 of 13

search next value with textbox in other sheet

  1. #1
    Forum Contributor
    Join Date
    12-18-2008
    Location
    Slovakia
    MS-Off Ver
    Office 2016
    Posts
    162

    search next value with textbox in other sheet

    Hi all,
    i tried ask on other forum in this thread
    http://www.vbaexpress.com/forum/showthread.php?t=28628
    but there nobody helps me with this (with other CODES helps me a lot), so i try it here.

    Pls. how can i define, that i want after pressing enter in textbox1 in sheet1 search name from sheet2. After pressing enter it will continue in search. The first founded value write to sheet1 in cell J2. The next value after next enter write it also in J2 and other enter ....
    thx a lot

    this is my try which doesnt works...
    Please Login or Register  to view this content.

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: search next value with textbox in other sheet

    To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

    Doing this will ensure you get the result you need!
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Forum Contributor
    Join Date
    12-18-2008
    Location
    Slovakia
    MS-Off Ver
    Office 2016
    Posts
    162

    Re: search next value with textbox in other sheet

    here it is,
    there are 2 sheet, in which i want to search...now it doesnt works, because i try use it for searching in other sheet.
    I have version, which works if data and textbox are in the same sheet, but i need it for more sheets.
    ONE important thing. I want only one MSGbox "not found" if is nothing founded. Now it return twice.

    thx a lot for your help
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    12-18-2008
    Location
    Slovakia
    MS-Off Ver
    Office 2016
    Posts
    162

    Re: search next value with textbox in other sheet

    nobody knows how to do this?

  5. #5
    Valued Forum Contributor rwgrietveld's Avatar
    Join Date
    09-02-2008
    Location
    Netherlands
    MS-Off Ver
    XL 2007 / XL 2010
    Posts
    1,671

    Re: search next value with textbox in other sheet

    Dear Danusko,

    You get 2 MsgBoxes as you missed a few End If's

    Next:
    Please Login or Register  to view this content.
    rSrch is a range that is STILL in worksheets("view") where you want it to be in worksheets("data1").

    I have the feeling that your code is not very efficient, but I can't work it around as I do not fully understand WHAT you want.

    here are my assumptions:
    Find all whole words in worksheet data1 range E5:E600
    Print "Complete string" in found order on worksheets view J2, J3, J4, Etc
    OR Print Cells reference where found

    Am I right?
    Last edited by rwgrietveld; 10-22-2009 at 03:48 AM.
    Looking for great solutions but hate waiting?
    Seach this Forum through Google

    www.Google.com
    (e.g. +multiple +IF site:excelforum.com/excel-general/ )

    www.Google.com
    (e.g. +fill +combobox site:excelforum.com/excel-programming/ )

    Ave,
    Ricardo

  6. #6
    Valued Forum Contributor rwgrietveld's Avatar
    Join Date
    09-02-2008
    Location
    Netherlands
    MS-Off Ver
    XL 2007 / XL 2010
    Posts
    1,671

    Re: search next value with textbox in other sheet

    You might try a WS function:
    Please Login or Register  to view this content.
    Look into the attached sheet.

    Pivot tables or Auto Filter is a good option as well.
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    12-18-2008
    Location
    Slovakia
    MS-Off Ver
    Office 2016
    Posts
    162

    Re: search next value with textbox in other sheet

    Quote Originally Posted by rwgrietveld View Post
    Dear Danusko,

    You get 2 MsgBoxes as you missed a few End If's

    Next:
    Please Login or Register  to view this content.
    rSrch is a range that is STILL in worksheets("view") where you want it to be in worksheets("data1").

    I have the feeling that your code is not very efficient, but I can't work it around as I do not fully understand WHAT you want.

    here are my assumptions:
    Find all whole words in worksheet data1 range E5:E600
    Print "Complete string" in found order on worksheets view J2, J3, J4, Etc
    OR Print Cells reference where found

    Am I right?


    what i want:
    - i write name or part of name to textbox in view and when i press „enter“ it will start to search this part or whole name in sheet data1 and data2 (column E)
    - if it find something, it will print it (only the first founded) to cell J2 in view (it print of course whole founded name – e.g. i will search „dan“, it will print the first e.g. „Dany“)
    - if i press „enter“ again it will continue in searching but not from begining. It will continue from „Dany“. If it will find other e.g „daniel“, it will print (overwrite) it to J2 in view

    i know, that my explaining are terible and thank you for your patience

  8. #8
    Valued Forum Contributor rwgrietveld's Avatar
    Join Date
    09-02-2008
    Location
    Netherlands
    MS-Off Ver
    XL 2007 / XL 2010
    Posts
    1,671

    Re: search next value with textbox in other sheet

    I have reworked it a little. Hope you like it.

    1) It finds all occurances
    2) Puts these in a ComboBox
    3) Selection of CB is set in J2

    Dont have to press enter no more
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    12-18-2008
    Location
    Slovakia
    MS-Off Ver
    Office 2016
    Posts
    162

    Re: search next value with textbox in other sheet

    IT LOOKS GOOD - nice IDEA

    only:
    - results are only from one sheet or?
    - value in textbox can be only one word (if i want to write whole name e.g. "joe doe"?)
    - what if are results more as is screen..?
    - when i delete all character in textbox it return error

    but it looks better as i wish if will be possible fix those bugs

  10. #10
    Valued Forum Contributor rwgrietveld's Avatar
    Join Date
    09-02-2008
    Location
    Netherlands
    MS-Off Ver
    XL 2007 / XL 2010
    Posts
    1,671

    Re: search next value with textbox in other sheet

    This is my final go. For more you just have to pay

    Good luck with it.

    The coding is not optimized (error handling and such, but it works)
    Attached Files Attached Files

  11. #11
    Forum Contributor
    Join Date
    12-18-2008
    Location
    Slovakia
    MS-Off Ver
    Office 2016
    Posts
    162

    Re: search next value with textbox in other sheet

    thank you very much for your help...i am now really closer to my idea...
    have a nice day

  12. #12
    Registered User
    Join Date
    11-09-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: search next value with textbox in other sheet

    Case "data1"
    dataRng = "Namesdata1"
    Case "data2"
    dataRng = "Namesdata2"


    With Range(dataRng)
    --------------------------------------
    It seems like it find a value from column E, what exactly "Namesdata1" for since there no value/sheet name as Names or data1. from developer tool and project explorer&properties window from microsoft VB menu, it dont show any link/range. What should i do, if i want to search in other column? thank you
    Last edited by acrylic26; 05-20-2013 at 05:27 AM.

  13. #13
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: search next value with textbox in other sheet

    Hi, acrylic26,

    Welcome to the Forum.

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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