Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 29

Thread: Need an advice for searching function

  1. #1

    Need an advice for searching function

    Hello, I am a new VBA programmer and need advice of all you for my very first excel VBA job required by my company.
    There is a file with two sheets that as UserForm and Product (which contains data).
    I am about to search value on Product by inputting by user at UserForm sheet. So that if the value match when clicking "Search" button then Goto the Range that matches value on Product.
    But the matter is the Product code is designed of many types like "73521005-06-07"( the input searching must have not "-" and single, it is shorted by 73521005,73521006,73521007 )
    The more detail in the file attached below. It drives me stuck. So i really need all your help for the first VBA. Thank you so so much.
    Sample_searching.xlsx

  2. #2
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Here is a Combobox solution. Trying to do this with a Textbox is fraught with problems in my opinion.
    Your data has duplicate entries. I've made no attempt to deal with that.
    Attached Files Attached Files
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  3. #3
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Try the attached.
    Lots of assumptions made.
    Attached Files Attached Files
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  4. #4
    A big thank you for great solution, thank you for all.

  5. #5
    Quote Originally Posted by p45cal View Post
    Try the attached.
    Lots of assumptions made.
    Hi , i dectected if in the Product Sample sheet has value like 7103A104-A105 will cause error debug. The code error at here
    DigitCount = Len(zz(UBound(zz)))
          For i = CLng(Right(zz(0), DigitCount)) To CLng(zz(UBound(zz)))
    So i think the code should be removed the alphabet chars.

  6. #6
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Yes, one of the assumptions is that there'd only be numbers after the delimiter (- or,).
    You'd have expected this entry to be 7103A104-105.
    But what if next time we see an entry like 7103A114-C156?
    I will look at correcting where the delimiter has been put, but I won't have much time in the next day or two.

    The real trouble is that the raw source data is highly inconsistent; I can foresee a continuous stream of work-arounds being needed.
    It is the source data that needs to be sorted.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  7. #7
    Quote Originally Posted by p45cal View Post
    Yes, one of the assumptions is that there'd only be numbers after the delimiter (- or,).
    You'd have expected this entry to be 7103A104-105.
    But what if next time we see an entry like 7103A114-C156?
    I will look at correcting where the delimiter has been put, but I won't have much time in the next day or two.

    The real trouble is that the raw source data is highly inconsistent; I can foresee a continuous stream of work-arounds being needed.
    It is that that needs to be sorted.
    Hi, thank for supporting me a lot, I tried to fix by below code
     Dim RegEx As Object
              Set RegEx = CreateObject("VBScript.RegExp")
               RegEx.Global = True
               RegEx.Pattern = "[A-Za-z]"
               zz(UBound(zz)) = RegEx.Replace(zz(UBound(zz)), "")
          DigitCount = Len(zz(UBound(zz)))
          For i = CLng(Right(zz(0), DigitCount)) To CLng(zz(UBound(zz)))
    By that way. it removed all alphabet char and skip to correct address.
    I totally agreed about the raw source data is highly inconsistent. It makes me stuck on it during this week.
    So i wondered if the source data is bigger, seems about over 1000 rows, does this logic excutes a lot of time, if yes , can you suggest me a solution to optimaze when data is big.
    Thanks so much for spending your time.

  8. #8
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    A quick look at your RegEx idea seems to be OK.
    It would trip up on something like 7103A104-3A105 'though.
    Regarding time with 1000 rows - you need to test it. If it's long, we can look at doing what mdmckillop did and establish the array once on activation of that sheet. I suspect it might be fast enough as it is.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  9. #9
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    My big concern would be a fumble finger typist like me getting an 8 digit number correct.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  10. #10
    Quote Originally Posted by p45cal View Post
    A quick look at your RegEx idea seems to be OK.
    It would trip up on something like 7103A104-3A105 'though.
    Regarding time with 1000 rows - you need to test it. If it's long, we can look at doing what mdmckillop did and establish the array once on activation of that sheet. I suspect it might be fast enough as it is.
    The main raw source is currently about 600 rows, and it takes approximately 5 - 7 seconds. I have just finished code integrated into my main source code as Worksheet_Changed Event and it looks fine now.
    So can you help me about this: The required of logic is not change but now, I want to split 2 sheets out to 2 workbooks, the one is UserForm, last is "Product Sample". How to active and skip to corect address of the "Product Sample" workbook when detected same value.

  11. #11
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    You'd better update us with versions of the workbooks involved and what you've got so far, otherwise we'd just be guessing, and probably wrongly.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  12. #12
    Quote Originally Posted by p45cal View Post
    You'd better update us with versions of the workbooks involved and what you've got so far, otherwise we'd just be guessing, and probably wrongly.
    The logic is not change, only split out to 2 workbooks, I wondered that how to do in searching on workbook1 and the return result at workbook 2.
    Here is 2 wbs.
    Attached Files Attached Files

  13. #13
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    There's no sense in having the same code in both workbooks.
    In the attached, code only in xlsm file.
    Both files need to be open in the same instance of Excel.
    On my old machine, it look less then 1 second to come up with results.
    If it is taking significantly longer with your setup, then we'll have to move to create a searchable array somewhere.
    Attached Files Attached Files
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  14. #14
    Quote Originally Posted by p45cal View Post
    There's no sense in having the same code in both workbooks.
    In the attached, code only in xlsm file.
    Both files need to be open in the same instance of Excel.
    On my old machine, it look less then 1 second to come up with results.
    If it is taking significantly longer with your setup, then we'll have to move to create a searchable array somewhere.
    Maybe the raw source of yours is too small and it takes no much time than the main source, But I just fixed by ignoring the rows null or with space or first letter of good code is alphabet, the more reason takes its slow that I clear a paragraph of code at "SpecialCells(2)", and it is faster now, over 1000 rows now only take 1-2 second at all.
    EDIT: So there is no way to excute if one of both files is not open the same Excel?

  15. #15
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    So there is no way to excute if one of both files is not open the same Excel?
    There are two recent posts regarding working between different instances of Excel
    http://www.vbaexpress.com/forum/show...l=1#post370263
    http://www.vbaexpress.com/forum/show...l=1#post368909
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  16. #16
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by rong3 View Post
    So there is no way to excute if one of both files is not open the same Excel?
    Well, if the only workbook open is Product_Sample , you'd have no textbox or button to press! If the only workbook open is Userform , the code could (with more complex code) find the data in the other workbook, but going back to basics and the requirement in your first post: "then Goto the Range that matches value", I don't see how this can be achieved without opening the second workbook!
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  17. #17
    Quote Originally Posted by p45cal View Post
    Well, if the only workbook open is Product_Sample , you'd have no textbox or button to press! If the only workbook open is Userform , the code could (with more complex code) find the data in the other workbook, but going back to basics and the requirement in your first post: "then Goto the Range that matches value", I don't see how this can be achieved without opening the second workbook!
    "then Goto the Range that matches value" is for checking the value is match or not, I have just solved that required open workbook before using it. Thank so much p45cal for helping me a lot during this week.

  18. #18
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by rong3 View Post
    is for checking the value is match or not
    If only checking for a match then try the attached.
    The first time you search for a value it asks you to identify the Product Sample file, the file is not opened in Excel, subsequent searches don't need that and are much faster.
    See comments in code too.
    Attached Files Attached Files
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  19. #19
    Quote Originally Posted by p45cal View Post
    If only checking for a match then try the attached.
    The first time you search for a value it asks you to identify the Product Sample file, the file is not opened in Excel, subsequent searches don't need that and are much faster.
    See comments in code too.
    Thanks so much @p45cal, I have already solved this problem.

  20. #20
    Hi @p45cal, Sorry for awaking the sleeping theard, but I get trouble on some product code, it always return at the first row, here is attached file and your old solution, Can you take a quick look and help me, thanks so much.
    VBAExpress60922Sample_searching.xlsm

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •