Closed Thread
Results 1 to 2 of 2

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

  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

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

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

    Welcome to the Forum, unfortunately:

    This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.

    Thread Closed.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

Closed Thread

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