Results 1 to 9 of 9

find textbox value in sheets and put all matching criteria in a listbox

Threaded View

  1. #1
    Registered User
    Join Date
    09-08-2014
    Location
    Medan
    MS-Off Ver
    2007
    Posts
    5

    find textbox value in sheets and put all matching criteria in a listbox

    Excuse me, I'm having a problem with this vba
    I'm new in vba for excel and I want to make a program which I can input data using userform and then search the data by using other form (I make 4 different userform). All the data is in one sheet, and the search criteria that I want to find is in column B.
    My problem is I can't make find code yet.
    I try to copy some code (just in search code) from excel-it.com database but I'm having trouble since it said that "object variabel or with block variable not set"

    Would you please tell me what is wrong with my code

    Dim Ws As Worksheet
    Dim MyData As Range
    Dim c As Range
    Dim rFound As Range
    Dim r As Long
    Dim rng As Range
    Dim oCtrl As MSForms.Control
    
    Private Sub cmbFind_Click()
        Dim strFind As String    'what to find
        Dim FirstAddress As String
        Dim f As Integer
    
        strFind = Me.TextBox1.Value    'what to look for
    
        With MyData
        .AutoFilter
            Set c = .Find(strFind, LookIn:=xlValues)
            If Not c Is Nothing Then    'found it
    
                With Me    'load entry to form
                    .TextBox2.Value = c.Offset(0, 1).Value
                    .TextBox3.Value = c.Offset(0, 2).Value
                    .TextBox4.Value = c.Offset(0, 3).Value
                    r = c.Row
                    f = 0
                End With
                FirstAddress = c.Address
                Do
                    f = f + 1    'count number of matching records
                    Set c = .FindNext(c)
                Loop While Not c Is Nothing And c.Address <> FirstAddress
                If f > 1 Then
                    Select Case MsgBox("There are " & f & " instances of " & strFind, vbOKCancel Or vbExclamation Or vbDefaultButton1, "Multiple entries")
    
                    Case vbOK
                        FindAll
                    Case vbCancel
                        'do nothing
                    End Select
    
                End If
            Else: MsgBox strFind & " not listed"    'search failed
            End If
        End With
    
    End Sub
    
    Sub FindAll()
        Dim wesTemp As Worksheet
        Dim strFind As String    'what to find
    
        strFind = Me.TextBox1.Value
    
        If Not Ws.AutoFilterMode Then MyData.AutoFilter
    
        MyData.AutoFilter Field:=1, Criteria1:=strFind
    
        Me.ListBox1.Clear
        For Each c In MyData.Columns(1).SpecialCells(xlCellTypeVisible)
            With ListBox1
                .AddItem c.Value
                .List(.ListCount - 1, 1) = c.Offset(0, 1).Value
                .List(.ListCount - 1, 2) = c.Offset(0, 2).Value
                .List(.ListCount - 1, 3) = c.Offset(0, 3).Value
                .List(.ListCount - 1, 4) = c.Offset(0, 4).Value
                .List(.ListCount - 1, 5) = c.Row
            End With
        Next c
    
    End Sub

    Thanks before
    Last edited by JBeaucaire; 10-16-2014 at 12:42 AM. Reason: Added missing CODE tags. Please read and follow the Forum Rules, link above in the menu bar. Thanks.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Highlight matching string in Listbox based on Textbox value
    By Rick_Stanich in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-26-2014, 11:04 AM
  2. Code to find matching record in listbox on sheet.
    By Nitefox in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-01-2014, 04:07 AM
  3. Delete from 2 sheets if matching criteria
    By motown in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 05-13-2009, 03:22 PM
  4. Matching Criteria Between Sheets?
    By motown in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-09-2008, 12:44 PM
  5. How can find a value using two different matching criteria?
    By Dinesh in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-09-2006, 07:25 PM

Tags for this Thread

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