Enter row number in listbox1 when value on worksheet found

[ipbr21054]

I am using the code below

On my userform i type in Textbox1 a value.
The code then looks for that value on my worksheet.
IAs each value is found i would like to have the row number put into listbox1

Please advise how we can this this.
Thanks

Private Sub FindTheValue_Click()
If TextBox1.Value = "" Then
  MsgBox "PLEASE TYPE A VALUE TO SEARCH FOR", vbCritical + vbOKOnly, "SEARCH BOX IS EMPTY"
  TextBox1.SetFocus
  
  Else
    Set Rng = ws.Range("A5:AB3000").Find(Me.TextBox1.Value, Rng) ' RANGE TO LOOK IN & TEXTBOX NAME FOR WHAT TO LOOK FOR
    If Not Rng Is Nothing Then
        ws.Activate
        Rng.Select
    Else
        MsgBox "NOTHING TO MATCH THE SEARCH VALUE", vbCritical, "VALUE NOT FOUND MESSAGE"
        Exit Sub
    End If
    End If
End Sub

 

[mumps]

Add a command button to your userform (CommandButton1). Paste this code into the userform code module. After you open the userform, enter a value in TextBox1 and click the commandbutton.

Private Sub CommandButton1_Click()
    Application.ScreenUpdating = False
    Dim fnd As Range, srcRng As Range, i As Long, sAddr As String, srcWS As Worksheet, dic As Object
    Set srcWS = Sheets("Sheet1")
    Set srcRng = srcWS.Range("A5", srcWS.Range("AB" & Rows.Count).End(xlUp))
    Set dic = CreateObject("scripting.dictionary")
    If TextBox1.Value = "" Then
        MsgBox "PLEASE TYPE A VALUE TO SEARCH FOR", vbCritical + vbOKOnly, "SEARCH BOX IS EMPTY"
    Else
        Set fnd = srcRng.Find(Me.TextBox1.Value, LookIn:=xlValues, lookat:=xlWhole)
        If Not fnd Is Nothing Then
            sAddr = fnd.Address
            Do
                If Not dic.exists(fnd.Row) Then
                    dic.Add fnd.Row, Nothing
                    ListBox1.AddItem fnd.Row
                End If
                Set fnd = srcRng.FindNext(fnd)
            Loop While fnd.Address <> sAddr
            sAddr = ""
        Else
            MsgBox "NOTHING TO MATCH THE SEARCH VALUE", vbCritical, "VALUE NOT FOUND MESSAGE"
        End If
    End If
    Application.ScreenUpdating = True
End Sub

Private Sub UserForm_Initialize()
    TextBox1.SetFocus
End Sub

 

[ipbr21054]

Hi,
Ive done that but straight away i see NOTHING TO MATCH THE SEARCH VALUE

 

[mumps]

I tested the macro in a dummy file and it worked properly. Make sure that the value you entered in TextBox1 actually exists in the source range. If you are still having problems, please upload a copy of your file (de-sensitized if necessary) to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.

 

[ipbr21054]

Ok
I will check again once back,
My range is A5 -AB then down the page
Sheet1 has been changed to DATABASE

 

[ipbr21054]

Just a thought should I not need a
Row number every time.

So allow the user to just press command button for the row they needed adding,

Leave original code as it was & then just a
Simple code on a separate command button

Thanks

 

[mumps]

I'm not sure what you mean. It would be easier to help if you can upload your file as I mentioned in Post #4 and explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data.

 

[ipbr21054]

On my userform I search APPLE
The 1st value of APPLE found is in cell B33

So the command button code should look at what cell-row is selected & just put it in the list box

We continue to search for the next APPLE of which is G10 so user presses command button again & info is added

So at present the list box should so far be showjng
B33
G10

 

[mumps]

When you click the command button, the macro will search for all occurrences of "APPLE" and insert all the row numbers in the listbox so in your example, the numbers 10 and 33 will be inserted in the listbox. As I already mentioned, the code worked properly in my tests. Upload your file to get further help.

 

[ipbr21054]

I understand but I e been advised we may not require all so thinking of a button to allow the user to select