Searching through a worksheet for text or value entered in a text box

  • Helllo....
    I am trying to search through an excel worksheet using the code below. I have a [highlight]Form[/highlight] containg a text boxes and a command button (SearchTxt, FindCmd). The VBA program is able to search through XL but (1) It does not set the next value or text in a the worksheet active
    (2) It only finds a value or text as an entire cell and not as a set of characters in a cell.


    I tried to use Cells.Find method that solves the above problems, but i get an error message-[highlight]Object variable not set (91) [/highlight].


    I would trully appreciate someones assitance...


    Thanks


    [vba]
    Private Sub FindCmd_Click()
    Dim Rng1 As Variant


    If SearchTxt.Text = "" Then
    MsgBox "Please enter Vendor Number.", vbOKOnly, "Error"
    End If


    Set Rng1 = Range("A1:F10000").Find(what:=SearchTxt.Text, Lookat:=xlWhole, _
    LookIn:=xlValues, SearchDirection:=xlNext)
    If Rng1 Is Nothing Then
    MsgBox "Cannot Find" & " " & SearchTxt.Text & ".", vbOKOnly, "Sorry"
    Else
    Rng1.Activate

    Exit Sub
    End If
    End Sub
    [/vba]

  • Re: Searching through a worksheet for text or value entered in a text box


    Try this

  • Re: Searching through a worksheet for text or value entered in a text box


    Thanks for the superfast reply


    I tried the code you posted but its not working (nothing happens). Plus, if somehow the active cell in excel is changed say from A1 to B1, when you click on the find commandbutton it selects the entire range and does not search for anything...: hence
    [vba]Range("A1:F10000")[/vba]



    Is there another safe haven for this?


    David

  • Re: Searching through a worksheet for text or value entered in a text box


    change


    "if Rng1 is nothing" to "if c is nothing"


    and


    "Rng1.Activate" to "c.Activate"


    in roy's above code, that should work


    also, if you want to find just a part of the cells content then change xlWhole to xlPart in the find statement

    :drum: The worst moment for the atheist is when he is really thankful and has nobody to thank.

  • Re: Searching through a worksheet for text or value entered in a text box


    Well, i made the changes but it still back to square one where it finds the first text or value and stops. It does not go to the next similar value within the worksheet.


    I am trying to create a program with a close to similar function as a regular search in excel. You do Ctrl+F, put in what you want to look for and it goes through the work sheet displaying any value or text (displaying it again-if it exists anywhere else).


    Thanks again for the reply...


    David :)

  • Re: Searching through a worksheet for text or value entered in a text box


    Hi,


    This looks like the same problem in the posting:


    "search form in vba for a search in 20 sheets"


    take a look at the examples there...


    Cheers,


    dr

  • Re: Searching through a worksheet for text or value entered in a text box


    Sorry, here's the rest of it...


    Basically your code does not repeat. It only looks for one instance and then stops. So here's one example of a 'looping' find. It combines some of your code with JIUK's edits and a loop I built a while back. I also set it up to populate a list box (but see my reference in my previous post for Andy Pope's code to select the found item by double clicking on it), Team effort or what!


    Cheers,


    dr

  • Re: Searching through a worksheet for text or value entered in a text box


    Try this amended code - it uses Findnext


  • Re: Searching through a worksheet for text or value entered in a text box


    Thanks guys its 90% functional now.... I used the following code...as posted by rbrhodes. One tiny problem i am having is making it search for characters in a cell/worksheet. Say if i search of the letter A in my worksheet it should find that letter whether its by itself or with other characters like the A in Apple. It should display texts within cells that contain that character.


    Is this making sense?


    David



    [vba]
    Private Sub FindCmd_Click()
    Dim Rng1 As Range, c As Range

    UserFrm.Listtxt.Clear

    If SearchTxt.Text = "" Then
    MsgBox "Please enter Vendor Number.", vbOKOnly, "Error"
    Else

    Set Rng1 = Range("A1:F10000")
    With Rng1
    Set c = .Find(what:=SearchTxt.Text, LookAt:=xlWhole, _
    LookIn:=xlValues, SearchDirection:=xlNext)
    If c Is Nothing Then
    MsgBox "Cannot Find " & SearchTxt.Text & ".", vbOKOnly, "Sorry"
    Else
    c.Activate
    UserFrm.Listtxt.AddItem c.Value
    found1 = c.Address
    End If
    End With

    Do While found1 <> foundx
    Cells.FindNext(After:=ActiveCell).Activate
    UserFrm.Listtxt.AddItem c.Value
    foundx = ActiveCell.Address
    Loop
    End If
    End Sub
    [/vba]

  • Re: Searching through a worksheet for text or value entered in a text box


    Hi,


    Took another look at the code, fixed one bug and revised to look at xlPart instead of xlWhole. See below or attached.


    Cheers,


    dr



    Also added a 'Close' button to the form.


    Cheers,


    dr

  • Re: Searching through a worksheet for text or value entered in a text box


    Sweet:thanx:


    That works perfectly now... But :) , if it aint too much to ask...is there anyway i can make the program show the cell name a value was found in? Like _


    '$A$10' format?



    David

  • Re: Searching through a worksheet for text or value entered in a text box


    :)
    I found a way to make the program display the found value cell #
    using the following code...


    [vba]
    UserForm1.ListTxt.AddItem ActiveCell.Value & " (" & _
    Firstcell.Address & " )"


    [/vba]


    :)

  • Re: Searching through a worksheet for text or value entered in a text box


    Hi there
    i got your form with code, it works the way i want with some modifications...........


    but in tern of knowing the vba language, i would like to know how did you get these parameters on the Userform:

    Code
    Listtxt and Searchtxt


    It is very interesting to me, you might know a url addrress that could contain information on what i want to know.


    you will be very helpful.........

    ******************
    [SIZE=6]Yours
    h [/SIZE]

    Your Help Is Highly Appreciated

  • Re: Searching through a worksheet for text or value entered in a text box


    Hi,


    There are many site on the web that attempt to explain VBA... I learned by starting with a beginners book then surfing the Web and visiting forums like this. Using examples of code written by others I learned something about writing my own - trial and error - (and the lousy help files).


    As for me I'm totally willing to answer questions if I can. The Userform for example. The Userform is named 'Userform1' (the default name). On the form are two text boxes. One of them has been named 'Listtxt' and the other is named 'Searchtxt'.


    Anything typed into the box 'Searchtxt' is used by the macro 'Find' command as Find.what:= Searchtxt.Text. Listtxt is populated using the command Userform1.Listtxt.AddItem (the_item_to_add_goes_here).


    To view the code You ned the Properties window open in the VBE and right click any item, choose 'View Code' or 'Properties'.


    Is that what you wanted to know?



    Cheers,


    dr

  • Re: Searching through a worksheet for text or value entered in a text box


    Hey Helmekki:


    Are you asking how to use AND or OR, to refer to two components on a userform? Like say you have two text boxes and you want to display a Msgbox if a user does not enter values in one, the other or both of the textboxes you can use the following code.


    Code
    If TypeTxt.Text = "" Or SearchTxt.Text = "" Then
            MsgBox "Please enter Type value and/or search value Number.", vbOKOnly, "Error"
      End if



    If you are looking for other websites you want to look at....send me an email i will write you back ([email protected])


    CHeerz|
    DaviD

  • Re: Searching through a worksheet for text or value entered in a text box


    Realy Thank u very very much............


    I appreciate your help and the time you gave me.

    ******************
    [SIZE=6]Yours
    h [/SIZE]

    Your Help Is Highly Appreciated

  • Re: Searching through a worksheet for text or value entered in a text box


    How would you be apple to select and display the entire row range of the search item located?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!