userform listbox - using find to populate

[ajm]

hi all, i'm using the following code to find a particular name in my workbook, and then return particular cell value from the sheets on which it is found.

Public Sub search()    Dim ws As Worksheet
    Dim rngFound As Range
    Dim strFirst As String
    Dim strSearch As String
    Dim strWSname As String
    Dim Excludedsheets As Variant, GetSearchArray As Variant
    Dim x As Variant
    Dim l As Long
   


    l = 1


 strSearch = "bugger"
    For Each ws In ActiveWorkbook.Sheets
    
    Excludedsheets = Array("Homepage", " ", "  ", "Trips", "Data", "First", "Last", "Members")
        x = Application.Match(ws.Name, Excludedsheets, 0)
        If IsError(x) Then
            l = l + 1


         
        Set rngFound = ws.Range("C:C").Find(strSearch, ws.Range("C4"), xlValues, xlWhole, xlByColumns)
        If Not rngFound Is Nothing Then
            strWSname = ws.Range("D2") & " " & ws.Range("g2")
            If InStr(1, ws.Name, " ", vbTextCompare) > 0 Then strWSname = strWSname
            strFirst = rngFound.Address
            Do
                GetSearchArray = GetSearchArray & "|" & strWSname
                Set rngFound = ws.UsedRange.Find(strSearch, rngFound, xlValues, xlPart)
            Loop While rngFound.Address <> strFirst
        End If
        End If
        
    Next ws


    If Len(GetSearchArray) > 0 Then GetSearchArray = Mid(GetSearchArray, 2)
    
     
    
   End Sub

How do i get the resulting array to populate a listbox on a userform? also, if the result is zero, how do i have "No Bookings" show up in the listbox?

 

[ajm]

**bump**

 

[ajm]

bump

 

[My Aswer Is This]

I would prefer you tell me what your wanting to do.

Showing us a script which you have but which is not doing what you want is not helpful to me.

So please provide specific details about what your wanting.

Please do not say read my script and you will see what I want.

And your post where you say the below is very vague.
Provide specifics.

hi all, i'm using the following code to find a particular name in my workbook, and then return particular cell value from the sheets on which it is found.
​

 

[dmt32]

Hi,
If your code is populating your string in required manner, then you should be able to change it from a Sub to a Function to return the values to your listbox


Following update to your code untested but hopefully, will give you something to work with

Public Function Search(ByVal strSearch As String) As Variant
    Dim ws As Worksheet
    Dim rngFound As Range
    Dim strFirst As String, strWSname As String
    Dim Excludedsheets As Variant, GetSearchArray As Variant
    Dim x As Variant
    
    Excludedsheets = Array("Homepage", " ", "  ", "Trips", "Data", "First", "Last", "Members")
    
    For Each ws In ActiveWorkbook.Sheets
        
        x = Application.Match(ws.Name, Excludedsheets, 0)
        If IsError(x) Then
            
            Set rngFound = ws.Range("C:C").Find(strSearch, ws.Range("C4"), xlValues, xlWhole, xlByColumns)
            If Not rngFound Is Nothing Then
                strWSname = ws.Range("D2") & " " & ws.Range("g2")
                If InStr(1, ws.Name, " ", vbTextCompare) > 0 Then strWSname = strWSname
                strFirst = rngFound.Address
                Do
                    GetSearchArray = GetSearchArray & "|" & strWSname
                    Set rngFound = ws.UsedRange.Find(strSearch, rngFound, xlValues, xlPart)
                Loop While rngFound.Address <> strFirst
            End If
        End If
        Set rngFound = Nothing
        Next ws
        
        If Len(GetSearchArray) > 0 Then
            GetSearchArray = Mid(GetSearchArray, 2)
            Search = Split(GetSearchArray, "|")
        Else
            Search = Array("No Bookings")
        End If
End Function

You will note that I have made your strSearch variable a parameter of the function. This will allow you to pass the search value to it as an argument.

Private Sub CommandButton1_Click()
    Me.ListBox1.List = Search("ajm")
End Sub

Hope Helpful


Dave

 

[ajm]

final code works, except it only shows the last entry found. it was working all along except my column C on each tan=b is hidden. so change where the find "lookin" looks, from xlvalues to xlformulas fixed that.

Public Sub search()
    Dim ws As Worksheet
    Dim rngFound As Range
    Dim strFirst As String
    Dim strSearch As String
    Dim strWSname As String
    Dim Excludedsheets As Variant, GetSearchArray As Variant
    Dim x As Variant
    Dim l As Long
   
    l = 1
 strSearch = frmBookings.cboMembName.Value
    For Each ws In ActiveWorkbook.Sheets
    
    Excludedsheets = Array("Homepage", " ", "  ", "Trips", "Data", "First", "Last", "Members")
        x = Application.Match(ws.Name, Excludedsheets, 0)
        If IsError(x) Then
            l = l + 1
         
        Set rngFound = ws.Range("C:C").Find(strSearch, ws.Range("C4"), xlFormulas, xlWhole, xlByColumns)
        If Not rngFound Is Nothing Then
            strWSname = ws.Range("D2") & " " & ws.Range("g2")
            If InStr(1, ws.Name, " ", vbTextCompare) > 0 Then strWSname = strWSname
            strFirst = rngFound.Address
            Do
                GetSearchArray = GetSearchArray & "|" & strWSname
                Set rngFound = ws.UsedRange.Find(strSearch, rngFound, xlFormulas, xlPart)
                
            Loop While rngFound.Address <> strFirst
        End If
        End If
        With frmBookings.LbxExistBook
        .Clear
        If strWSname <> "" Then
            .AddItem strWSname
        End If
        End With

    Next ws
   ' If Len(GetSearchArray) > 0 Then GetSearchArray = Mid(GetSearchArray, 2)
    
   
    
   End Sub

 

[ajm]

I would prefer you tell me what your wanting to do.

Showing us a script which you have but which is not doing what you want is not helpful to me.

So please provide specific details about what your wanting.

Please do not say read my script and you will see what I want.

And your post where you say the below is very vague.
Provide specifics.

hi all, i'm using the following code to find a particular name in my workbook, and then return particular cell value from the sheets on which it is found.
​

Thanks or responding. I don't think its vague at all. I was trying to populate a userform listbox. Using Find to locate a value in the workbook. when its found, return the contents of a cell from the sheet on which the found value was located. Code is always provided to give context. I worked it out myself though so no need for further responses. Cheers.

 

[ajm]

final code works, except it only shows the last entry found. it was working all along except my column C on each tan=b is hidden. so change where the find "lookin" looks, from xlvalues to xlformulas fixed that.

Public Sub search()
    Dim ws As Worksheet
    Dim rngFound As Range
    Dim strFirst As String
    Dim strSearch As String
    Dim strWSname As String
    Dim Excludedsheets As Variant, GetSearchArray As Variant
    Dim x As Variant
    Dim l As Long
   
    l = 1
 strSearch = frmBookings.cboMembName.Value
    For Each ws In ActiveWorkbook.Sheets
    
    Excludedsheets = Array("Homepage", " ", "  ", "Trips", "Data", "First", "Last", "Members")
        x = Application.Match(ws.Name, Excludedsheets, 0)
        If IsError(x) Then
            l = l + 1
         
        Set rngFound = ws.Range("C:C").Find(strSearch, ws.Range("C4"), xlFormulas, xlWhole, xlByColumns)
        If Not rngFound Is Nothing Then
            strWSname = ws.Range("D2") & " " & ws.Range("g2")
            If InStr(1, ws.Name, " ", vbTextCompare) > 0 Then strWSname = strWSname
            strFirst = rngFound.Address
            Do
                GetSearchArray = GetSearchArray & "|" & strWSname
                Set rngFound = ws.UsedRange.Find(strSearch, rngFound, xlFormulas, xlPart)
                
            Loop While rngFound.Address <> strFirst
        End If
        End If
        With frmBookings.LbxExistBook
        .Clear
        If strWSname <> "" Then
            .AddItem strWSname
        End If
        End With

    Next ws
   ' If Len(GetSearchArray) > 0 Then GetSearchArray = Mid(GetSearchArray, 2)
    
   
    
   End Sub

as for this, should have said "column C on each tab" . also, had my add item in the wrong place. all fixed now and working.

Many thanks to those above who responded.