Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Searching

  1. #1
    Guest

    Default


    Does anyone know how to create a userform with 3 textboxs for searching the excel with repeated values. So user can enter 3 values to search a specific data or enter 1 or 2 values to find the first value found in the excel. The worksheet has the following format

    Weight Color Code
    ------ ----- ----
    30 40 50
    ------ ----- ----
    30 20 50
    ------------------
    30 40 30




  2. #2
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I don't understand the question, so I'm guessing that others may be confused as well. Do you want to find, for example, three values that are located, in order, in three adjacent cells in the same row? Some clarification would be helpful.
    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  3. #3
    Guest

    Default

    I have three columns B, C, D. All kinds of number(Integer, decimal) and some repeated numbers are randomly located in those three columns. The format is something like the following

    Color Weight Code
    -----------------
    33 44 23.3
    .................
    .................
    .................
    Color Weight Code
    -----------------
    44 33 34
    .................
    .................
    .................(....means blank cells)
    I need to create a userform with three textboxs to have the user enter three values
    and have the cell either Color, Weight or Code selected according to the values entered by the user.

    Can someone please help me

    If you need more information please just let me know
    Thank you in advance

  4. #4
    Guest

    Default

    Hi,

    I posted a response to this question this morning, but it seems to have gotten lost, so here is the solution again. This solution assumes you have a custom userform with three textboxes, named ColorTB, WeightTB, and CodeTB, and also have a commandbutton named FindBtn, and that the sheet you want to search on is active at the time the form is loaded. It also assumes that the 3 data cells are directly under (in the next row from) the Color/Weight/Code headings.


    Private Sub FindBtn_Click()
    Dim Color As String
    Dim Weight As String
    Dim Code As String

    Color = ColorTB.Text
    Weight = WeightTB.Text
    Code = CodeTB.Text

    'Search active worksheet for line with these values

    'First find lines with Color/Weight/Code header
    Dim Cell As Range
    Dim FirstFind As Range
    Set Cell = Nothing

    Do
    If Cell Is Nothing Then
    Set Cell = Columns(2).Find("Color", LookIn:=xlValues)
    Set FirstFind = Cell
    End If
    If Not Cell Is Nothing Then
    'color header found--check for weight/code header
    If Cell.Offset(0, 1) = "Weight" And Cell.Offset(0, 2) = "Code" Then
    'valid complete header found--now check data values
    If Cell.Offset(1, 0) = Color And _
    Cell.Offset(1, 1) = Weight And _
    Cell.Offset(1, 2) = Code Then
    'value found--select these cells
    Range(Cell.Offset(1), Cell.Offset(1, 2)).Select
    Exit Sub
    End If
    End If
    End If
    Set Cell = Columns(2).FindNext(Cell)
    Loop Until Cell.Address = FirstFind.Address
    MsgBox "Values not found", vbexplanation, "Search results"
    End Sub

    This requires and EXACT match in all three cells to all three textboxes. Of course this code goes into the event code module associated with the userform.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •