+ Reply to Thread
Results 1 to 4 of 4

Show Search results in userform

Hybrid View

  1. #1
    Registered User
    Join Date
    02-21-2011
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    30

    Show Search results in userform

    Hi, I am using the file NameNo Data (2).xls‎ and it is working for my needs, but for the liofe of me I can't figure out why it will only return 6 column worth of row data. I have tried to make what I thought were the right change by adding additional entries as listed her but it stops at 6 column still. any ideeas

    With Me.ListBox1
                        .AddItem c.Value
                        .List(.ListCount - 1, 0) = c.Offset(0, x).Address    'record number
                        .List(.ListCount - 1, 1) = c.Offset(0, x).Value  'surname
                        .List(.ListCount - 1, 2) = c.Offset(0, (x + 1)).Value  'first name
                        .List(.ListCount - 1, 3) = c.Offset(0, (x + 2)).Value    'DOB
                        .List(.ListCount - 1, 4) = c.Offset(0, (x + 3)).Value    'address
                        .List(.ListCount - 1, 5) = c.Offset(0, (x + 4)).Value    'date spoken to
                        .List(.ListCount - 1, 6) = c.Offset(0, (x + 5)).Value    'notes
                        .List(.ListCount - 1, 7) = c.Offset(0, (x + 6)).Value
                        .List(.ListCount - 1, 8) = c.Offset(0, (x + 7)).Value
                        .List(.ListCount - 1, 9) = c.Offset(0, (x + 8)).Value
                        '.List(.ListCount - 1, 10) = c.Offset(0, (x + 9)).Value
                        '.List(.ListCount - 1, 11) = c.Offset(0, (x + 10)).Value
                        '.List(.ListCount - 1, 12) = c.Offset(0, (x + 11)).Value
                        '.List(.ListCount - 1, 13) = c.Offset(0, (x + 12)).Value
                        '.List(.ListCount - 1, 14) = c.Offset(0, (x + 13)).Value
                        '.List(.ListCount - 1, 15) = c.Offset(0, (x + 14)).Value
                        '.List(.ListCount - 1, 16) = c.Offset(0, (x + 15)).Value
                        '.List(.ListCount - 1, 17) = c.Offset(0, (x + 16)).Value
                        '.List(.ListCount - 1, 18) = c.Offset(0, (x + 17)).Value
                        '.List(.ListCount - 1, 19) = c.Offset(0, (x + 18)).Value
                        '.List(.ListCount - 1, 20) = c.Offset(0, (x + 19)).Value
                        '.List(.ListCount - 1, 21) = c.Offset(0, (x + 20)).Value
    
                    End With
    Moderator's Edit: Use code tags when posting code. To do so in future, select your code and click on the # icon at the top of your post window.


    Sorry, I forgot my code tag
    Last edited by StartingOut; 12-20-2012 at 08:59 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    11-15-2008
    Location
    ph
    MS-Off Ver
    2007/2010/2016
    Posts
    479

    Re: Show Search results in userform

    Hi -

    Please check your listbox property and look for ColumnCount

    Regards,
    Event
    Last edited by event21; 12-20-2012 at 09:45 AM.

  3. #3
    Registered User
    Join Date
    02-21-2011
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Show Search results in userform

    A no brainier and I had no brain, Thank you Event21

  4. #4
    Registered User
    Join Date
    02-21-2011
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Show Search results in userform

    Sorry folks, I know this is code related. so I am stuck, I was able to modify this code for my use in my project but I am getting a failure at this point, its as if all the line items that have ListCount with a double digit don't work I commented each line out to test and every one fails with error "could not set the list property, Invalid property value"

    .List(.ListCount - 1, 10) = c.Offset(0, (x + 9)).Value

    Private Sub CommandButton1_Click()
    'SEARCH
        Dim c As Variant
        Dim Col As Variant
        Dim Data As Variant
        Dim FirstAddx As String
        Dim FoundIt As Range
        Dim I As Integer
        Dim R As Long
        Dim Rng As Range
        Dim RngEnd As Range
        Dim SrcWks As Worksheet
        Dim strFind As String    'what to find
        Dim x As Long
    
    
        Set SrcWks = Worksheets("Membership Sales")
        Set MyData = SrcWks.Range("A2").CurrentRegion
    
        With Frame1.Controls
            For I = 0 To .Count - 1
                If .Item(I).Value = True Then
                    BtnName = .Item(I).Name
                    Exit For
                End If
            Next I
        End With
    
        Select Case BtnName
        Case "OptionButton1"
            strFind = TextBox1
            x = 0 - 4
            Col = 5: Data = TextBox1: GoSub DataSearch
            'Col = 3: Data = TextBox2: GoSub DataSearch
        Case "OptionButton2"
            strFind = TextBox1
            x = 0 - 5
            Col = 6: Data = TextBox1: GoSub DataSearch
        Case "OptionButton3"
            strFind = TextBox1
            'x = 0 - 5
            Col = 1: Data = TextBox1: GoSub DataSearch
        Case "OptionButton4"
            strFind = TextBox1
            x = 0 - 19
            Col = 20: Data = TextBox1: GoSub DataSearch
        End Select
    
        Exit Sub
    
    DataSearch:
        With SrcWks
            Set Rng = .Cells(2, Col)
            Set RngEnd = .Cells(Rows.Count, Col).End(xlUp)
            Set RngEnd = IIf(RngEnd.Row < Rng.Row, Rng, RngEnd)
            Set Rng = .Range(Rng, RngEnd)
        End With
    
        Data = Trim(Data)
        Set FoundIt = Rng.Find(What:=Data, After:=Rng.Cells(1, 1), _
                               LookIn:=xlFormulas, LookAt:=CheckBox2.Value + 2, _
                               SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                               MatchCase:=CheckBox1.Value)
        If Not FoundIt Is Nothing Then
            FirstAddx = FoundIt.Address
            If CheckBox3.Value = False Then Exit Sub
            Set FoundIt = Rng.FindNext(FoundIt)
            R = R + 1
    
    
    
            With SrcWks
                If Not .AutoFilterMode Then MyData.AutoFilter
                MyData.AutoFilter Field:=Col, Criteria1:=strFind
                Set Rng = MyData.Columns(Col).Cells.SpecialCells(xlCellTypeVisible)
                Me.ListBox1.Clear
                For Each c In Rng
                    With Me.ListBox1
                        .AddItem c.Value
                        .List(.ListCount - 1, 0) = c.Offset(0, x).Address    'record number
                        .List(.ListCount - 1, 1) = c.Offset(0, x).Value  'surname
                        .List(.ListCount - 1, 2) = c.Offset(0, (x + 1)).Value  'first name
                        .List(.ListCount - 1, 3) = c.Offset(0, (x + 2)).Value    'DOB
                        .List(.ListCount - 1, 4) = c.Offset(0, (x + 3)).Value    'address
                        .List(.ListCount - 1, 5) = c.Offset(0, (x + 4)).Value    'date spoken to
                        .List(.ListCount - 1, 6) = c.Offset(0, (x + 5)).Value    'notes
                        .List(.ListCount - 1, 7) = c.Offset(0, (x + 6)).Value
                        .List(.ListCount - 1, 8) = c.Offset(0, (x + 7)).Value
                        .List(.ListCount - 1, 9) = c.Offset(0, (x + 8)).Value
                        '.List(.ListCount - 1, 10) = c.Offset(0, (x + 9)).Value
                        '.List(.ListCount - 1, 11) = c.Offset(0, (x + 10)).Value
                        '.List(.ListCount - 1, 12) = c.Offset(0, (x + 11)).Value
                        '.List(.ListCount - 1, 13) = c.Offset(0, (x + 12)).Value
                        '.List(.ListCount - 1, 14) = c.Offset(0, (x + 13)).Value
                        '.List(.ListCount - 1, 15) = c.Offset(0, (x + 14)).Value
                        '.List(.ListCount - 1, 16) = c.Offset(0, (x + 15)).Value
                        '.List(.ListCount - 1, 17) = c.Offset(0, (x + 16)).Value
                        '.List(.ListCount - 1, 18) = c.Offset(0, (x + 17)).Value
                        '.List(.ListCount - 1, 19) = c.Offset(0, (x + 18)).Value
                        '.List(.ListCount - 1, 20) = c.Offset(0, (x + 19)).Value
                        '.List(.ListCount - 1, 21) = c.Offset(0, (x + 20)).Value
    
                    End With
                Next c
            End With
            '    Me.EnableEvents = True
    
    
    
    
    
            '                    Loop While FoundIt.Address <> FirstAddx And Not FoundIt Is Nothing
            '        Me.ListBox1.RowSource = DstWks.UsedRange.Address(external:=True)
        Else
            MsgBox "No Match was found for '" & Data & " '", vbExclamation
        End If
        SrcWks.AutoFilterMode = False
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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