VbA searching for records with filtered data

[jocotterellash]

Hello,

I wonder if anyone can help me? I have a search sub function that works pretty well (finds name of a given person in a list box), however I only want it to look at my filtered data on my sheet called shstaff.
At the moment it looks at every record on shstaff even when my visible records on shstaff are filtered.
I'm fairly new to VBA with excel so learning as I go along. I would be really grateful for any advice.
Many thanks in advance!



sub findSearchResults()


Dim emplr As Variant
Dim cursearch As Variant
Dim x As Variant
Dim curRowData As Variant

emplr = shStaff.Cells.SpecialCells(xlCellTypeVisible)(Rows.Count, 1).End(xlUp).row


'clear lbx
Me.lbxResults.Clear

cursearch = Me.tbSearch

For x = 2 To emplr
curRowData = shStaff.Cells.SpecialCells(xlCellTypeVisible)(x, 1) & " " & shStaff.Cells.SpecialCells(xlCellTypeVisible)(x, 2) & " " & shStaff.Cells.SpecialCells(xlCellTypeVisible)(x, 3)
If InStr(1, curRowData, cursearch, vbTextCompare) <> 0 Then
'found it; add to listbox
Me.lbxResults.AddItem shStaff.Cells.SpecialCells(xlCellTypeVisible)(x, 1)
Me.lbxResults.List(Me.lbxResults.ListCount - 1, 1) = shStaff.Cells.SpecialCells(xlCellTypeVisible)(x, 3) & ", " & shStaff.Cells.SpecialCells(xlCellTypeVisible)(x, 2)
Me.lbxResults.List(Me.lbxResults.ListCount - 1, 2) = shStaff.Cells.SpecialCells(xlCellTypeVisible)(x, "n")
Me.lbxResults.List(Me.lbxResults.ListCount - 1, 3) = shStaff.Cells.SpecialCells(xlCellTypeVisible)(x, "I")
End If

Next x



End Sub

 

[NoSparks]

I would approach this using the range.find method and findnext
something along the lines of this

Sub FindSearchResults()

    Dim cursearch As Variant
    Dim fndRng As Range
    Dim firstAddress As String

' clear lbx
Me.lbxResults.Clear
' what to seach for
cursearch = Me.tbSearch

With Sheets("shStaff").Range("A:A")
    Set fndRng = .Find(What:=cursearch, After:=.Cells(1, 1), LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    If Not fndRng Is Nothing Then
        firstAddress = fndRng.Address
        Do
            ' if visible populate listbox
            If fndRng.EntireRow.Hidden = False Then
                'message box for testing purposes only
                MsgBox cursearch & " is visible on row " & fndRng.Row
                'populate listbox
                With Me.lbxResults
                    .AddItem fndRng.Value
                    .List(.ListCount - 1, 1) = fndRng.Offset(, 2).Value & ", " & fndRng.Offset(, 1).Value
                    .List(.ListCount - 1, 2) = fndRng.Offset(, 13).Value
                    .List(.ListCount - 1, 3) = fndRng.Offset(, 8).Value
                End With
            End If
            ' keep searching
            Set fndRng = .FindNext(fndRng)
        Loop While Not fndRng Is Nothing And fndRng.Address <> firstAddress
    End If
End With

End Sub

your use of Inst makes me think you are looking for a partial match, if not
change LookAt:=xlPart to LookAt:=xlWhole

 

[jocotterellash]

Thank you for reading my question and replying with a potential solution. I will give this ago. Thanks again.

 

[jocotterellash]

I would approach this using the range.find method and findnext
something along the lines of this

Sub FindSearchResults()

    Dim cursearch As Variant
    Dim fndRng As Range
    Dim firstAddress As String

' clear lbx
Me.lbxResults.Clear
' what to seach for
cursearch = Me.tbSearch

With Sheets("shStaff").Range("A:A")
    Set fndRng = .Find(What:=cursearch, After:=.Cells(1, 1), LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    If Not fndRng Is Nothing Then
        firstAddress = fndRng.Address
        Do
            ' if visible populate listbox
            If fndRng.EntireRow.Hidden = False Then
                'message box for testing purposes only
                MsgBox cursearch & " is visible on row " & fndRng.Row
                'populate listbox
                With Me.lbxResults
                    .AddItem fndRng.Value
                    .List(.ListCount - 1, 1) = fndRng.Offset(, 2).Value & ", " & fndRng.Offset(, 1).Value
                    .List(.ListCount - 1, 2) = fndRng.Offset(, 13).Value
                    .List(.ListCount - 1, 3) = fndRng.Offset(, 8).Value
                End With
            End If
            ' keep searching
            Set fndRng = .FindNext(fndRng)
        Loop While Not fndRng Is Nothing And fndRng.Address <> firstAddress
    End If
End With

End Sub

your use of Inst makes me think you are looking for a partial match, if not
change LookAt:=xlPart to LookAt:=xlWhole

Hello,

I have tried this however it didn't like the first line With Sheets("shStaff").Range("A:A")

Sorry, i should of explained in my first post, to get the data to the list box for searching I have this code:

Public Function getrange() As range
Set getrange = shStaff.range("A2").CurrentRegion
Set getrange = getrange.Offset(1).Resize(getrange.Rows.Count - 1)
End Function

Plus this code in my user form:

Private Sub UserForm_Initialize()
' Fill the listbox
Call AddDataToListbox
End Sub

' Add the data to the ListBox using RowSource
Private Sub AddDataToListbox()
'Get the data range
Dim rg As range
Set rg = getrange

'Link the data to the ListBox
With listboxStaff
.RowSource = rg.Address(External:=True)
.columnCount = rg.Columns.Count
.ColumnWidths = "30;85;85;150;150;100;30;30;30;30"
.ColumnHeads = True
.ListIndex = 0
End With
End Sub

I've looked for answers to this, I've seen that some VBA experts say you must not use rowsource when filtering data.
Perhaps I need a new approach to linking and adding data to my listbox other than rowsource?

Many thanks again I really appreciate any advice.

 

[NoSparks]

I have tried this however it didn't like the first line With Sheets("shStaff").Range("A:A")

oops: shStaff is the sheet code name and I put it as if it was the name on the tab, should just be

With shStaff.Range("A:A")

You may (or may not) be interested in this site regarding ListBoxes

 

[jocotterellash]

oops: shStaff is the sheet code name and I put it as if it was the name on the tab, should just be

With shStaff.Range("A:A")

You may (or may not) be interested in this site regarding ListBoxes

Thanks a million, this seems to be working for me now - thanks also for link to listboxes that's very useful information.

 

[NoSparks]

You're welcome

 

[jocotterellash]

Hello,

I'm really sorry to bother you again. I've been trying to do this myself, however I've stumbled somewhat.
Basically, what i wish to do is: after I get me name that I've been searching in my listbox, I then want to select a record (in listbox) by clicking on it (on the onclick) event.
So for the record I've clicked on i want to change results in the raw data in shstaff in column five value to yes. The below code used to work, however I've since changed the listbox and not sure how to reapply this now I've changed the search method.

shStaff.Cells(Me.lbxResults.Value, 5) = "yes"

the error i get is like a formatting one, like i'm trying to put a text answer into number formatted field.

If you or anyone could point me along the lines I'd be very grateful, many thanks again in advance.

 

[NoSparks]

I'd add another column to the listbox that holds the row number

Sub FindSearchResults()
    
Dim cursearch As Variant, fndRng As Range, firstAddress As String

' clear lbx
Me.lbxResults.Clear
' what to seach for
cursearch = Me.tbSearch

With shStaff.Range("A:A")   '<<<---- sheet code name
    Set fndRng = .Find(What:=cursearch, After:=.Cells(1, 1), LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    If Not fndRng Is Nothing Then
        firstAddress = fndRng.Address
        Do
            ' if visible populate listbox
            If fndRng.EntireRow.Hidden = False Then
                'populate listbox
                With Me.lbxResults
                    .AddItem fndRng.Value
                    .List(.ListCount - 1, 1) = fndRng.Offset(, 2).Value & ", " & fndRng.Offset(, 1).Value
                    .List(.ListCount - 1, 2) = fndRng.Offset(, 13).Value
                    .List(.ListCount - 1, 3) = fndRng.Offset(, 8).Value
                    ' add another column with the row number
                    .List(.ListCount - 1, 4) = fndRng.Row
                End With
            End If
            ' keep searching
            Set fndRng = .FindNext(fndRng)
        Loop While Not fndRng Is Nothing And fndRng.Address <> firstAddress
    End If
End With

End Sub

and use that in listbox click

Private Sub lbxResults_Click()

    shStaff.Cells(Me.lbxResults.Column(4), 5).Value = "yes"

End Sub

 

[jocotterellash]

This works brilliantly! Thanks again for taking the time to help me out.