userform listbox - using find to populate

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
2,004
Office Version
  1. 365
Platform
  1. Windows
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.

Code:
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?
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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.
 
Upvote 0
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

Code:
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.


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


Hope Helpful


Dave
 
Last edited:
Upvote 0
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.

Code:
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
 
Upvote 0
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.
 
Upvote 0
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.

Code:
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.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top