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

David412 · May 26th 2005

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]

**royUK** · May 26th 2005

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

Try this

Code

Private Sub FindCmd_Click()
    Dim Rng1 As Range, c As Range
     
    If SearchTxt.Text = "" Then
        MsgBox "Please enter Vendor Number.", vbOKOnly, "Error"
    End If
     
    Set Rng1 = Range("A1:F10000")
    With Rng1
    Set c = .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 With

Display More

David412 · May 26th 2005

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

neuralgroove · May 26th 2005

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

David412 · May 27th 2005

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

rbrhodes · May 27th 2005

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

rbrhodes · May 27th 2005

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

**royUK** · May 27th 2005

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

Try this amended code - it uses Findnext

Code

Private Sub FindCmd_Click()
    Dim Rng1 As Range, c As Range
    Dim strAddress As Range
    Dim MyVar As String
    
    Set MyVar = Me.searchtxt.Text
    If MyVar = "" Then
      MsgBox "Please enter Vendor Number.", vbOKOnly, "Error"
    End If
     
    Set Rng1 = Range("A1:F10000") 'I would actually use CurrentRegion or usedRange here
    With Rng1
        Set c = .Find(what:=MyVar, LookAt:=xlPart, _
        LookIn:=xlValues, SearchDirection:=xlNext)
        If Not c Is Nothing Then
           strAddress = c.Address
        Do
          c.Interior.ColorIndex = 3 'highlight foun cell, amend action to suit
          Set c = .FindNext(c)
           Loop While Not c Is Nothing And c.Address <> strAddress
         Else: MsgBox "Cannot Find " & searchtxt.Text & ".", vbOKOnly, "Sorry"
        End If


    End With

Display More

David412 · May 27th 2005

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]

rbrhodes · May 29th 2005

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

Code

Private Sub CommandButton1_Click()
    Dim Rng1 As Range, c As Range
     
    UserForm1.Listtxt.Clear
     
    If Searchtxt.Text = "" Then
        MsgBox "Please enter Vendor Number.", vbOKOnly, "Error"
    End If
     
    Set Rng1 = Range("A1:F10000")
    With Rng1


'This line was: 'Lookat:= xlWhole'  now is 'Lookat:= xlPart'


        Set c = .Find(what:=Searchtxt.Text, LookAt:=xlPart, _
        LookIn:=xlValues, SearchDirection:=xlNext)
            If c Is Nothing Then
                MsgBox "Cannot Find " & Searchtxt.Text & ".", vbOKOnly, "Sorry"
            Else
                c.Activate
                UserForm1.Listtxt.AddItem c.Value
                found1 = c.Address
            End If
    End With
        
    Do While found1 <> foundx
        Cells.FindNext(After:=ActiveCell).Activate


'The old version of the section below would do 2 things incorrectly -
' It would repeatedly fill the listbox with c.value instead of the current cell
' value...my oops.  And it would add the last item found to the list - which
' is actually the first value, again!  This code fixes that.




'***** begin bugfix *******


        foundx = ActiveCell.Address
        If found1 = foundx Then
            Exit Sub
        End If
        UserForm1.Listtxt.AddItem ActiveCell.Value


'***** end bugfix *******


    Loop


End Sub

Display More

Also added a 'Close' button to the form.

Cheers,

dr

David412 · May 29th 2005

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

David412 · May 29th 2005

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]

h · Jun 6th 2005

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.........

rbrhodes · Jun 7th 2005

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

David412 · Jun 7th 2005

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

h · Jun 7th 2005

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.

rgrone · Jul 10th 2006

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?

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

Participate now!

Share

Users Viewing This Thread