Userform Help

  • Hello.


    I am currenlty doing a little project for my wifes workplace. It is for a stock search utility. They have given me the spreadsheet with all the stock items.
    The layout for the sheet is COLUMN A contains the Site of where the stock is..(42 rows) COLUMN B has the names of who manages the Site... (also 42 rows).. and the stock is from Column C2:DD43


    I have devised a basic user form with 1 input text box ("txtItem") a search button ("cmdFind") and 2 other textboxes to display the results, ("txtSite" & "txtManage") which displays the results. I just cant seem to find the right syntax on how to search the 'DATA' in C2:DD43 and get the results someone. I have looked at various videos and other posts but they dont really explain the way I need to do it.


    So basically if I type in SOCKS it will return where the stock is, (ColumnA) and who manages it (Column B)!


    any help appreciated!

  • Thanks for the link Carim, However its not quite what I'm looking for, my form already has data on it (that was sent to me) It has a search button which searches for a match in the sheet and returns the value from Cells A and B into the textboxes, The value would be from Column A & B; row whatever!.. Its the 'whatever' bit that I'm looking to achieve the results!
    Appreciation for the help!

  • Hello,


    It would appear your question is not related to UserForms ...:wink:


    You are after the row number where 'Socks' is located ...


    Below is a tiny code for tests purposes:


    Code
    1. Sub rwMatch()
    2. Dim rw As Long
    3. rw = Application.Match("Socks", Sheet1.Range("A:A"), 0)
    4. MsgBox rw
    5. End Sub


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • ...In a sense yes! however!! Its to get the result back to textbox1 when the user has pressed the command button!, (Find) I could have used a llistbox, so user can find that way but there is a vast amount of data where 'socks' could be located (range C2:DD43)


    I have been playing with the code below, but when I press find I get the 'No Match Found' error message, but I know the item I entered is there!. also the search can include numbers and text. e.g. 'SOCKS 15b'





    Im just trying to correct this code... Hope this helps!


    Appreciated :)

  • Hello,


    Below is your code with a tiny modification ...



    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Nearly.. at least something is populating textbox2 now! but it needs to be the value of colum A (site).. I think it could be something to do with the FIND statement.. what I need is the correct syntax for -


    A) FIND WHAT - The value in textbox1 (Socks, which in this case socks is found in COLUMN AL:39 (Which I have now set to a named range "ITEMS"...=Sheet1!$C$2:$HX$22)
    B)IF FOUND Then lookup COLUMN A (SITE) and go down to row 39 and put value from A39 in Textbox2 ( in this case the value is HubLon)


    Hope that explains better!.. (you probably understand.. in no way making it seem like you are 'Sucking eggs' but I still have probs when trying to explain it to google!!! :) )
    I have looked different methods also like MATCH, INDEX,VLOOKUP, but struggling to grasp some of it tbh. :duh: (need to keep reading more!)


    Regards

  • Hello,


    Could you attach your workbook ...


    It would greatly facilitate things ...:wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hello,


    Attached is your test file ...:wink:


    Hope this will help

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Ingo Ingo... I believe that was a 'Typo'.....:)


    Carim... You are a god!!! :yourock::yourock:...


    I will ensure to study the code, in case needed for anything else... thanks for all your help..
    (Just make sure you stick about!!)...:cheers:

  • Quite glad to hear you could fix your problem ...:wink:


    Many Thanks ... for your Thanks ...AND for the Like ... :smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)