If not correct value found then continue search

[ipbr21054]

Hi,
I have a userform which will search for my value in TextBox1.
The search finds the first value BUT its not the one i require.

On my userform i have a command button which i hope to press so the search will just continue & look for the next value.
Please advise code to place on command button.

This is what i have so far.

Private Sub ClearSearchField_Click()
TextBox1.Value = ""
TextBox1.SetFocus
End Sub

Private Sub CloseForm_Click()
Unload DatabaseSearch
End Sub

Private Sub ContinueSearch_Click()

End Sub

Private Sub FindTheValue_Click()
Dim ws As Worksheet
Dim Rng As Range
Set ws = Sheets("DATABASE") ' CHANGE SHEET NAME TO SUIT
Set Rng = ws.Range("A5:AB3000").Find(Me.TextBox1.Value) ' 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 Sub

Private Sub TextBox1_Change()
TextBox1 = UCase(TextBox1)
End Sub

Private Sub UserForm_Initialize()

    Me.StartUpPosition = 0
    Me.Top = Application.Top + 135  ' MARGIN FROM TOP OF SCREEN
    Me.Left = Application.Left + Application.Width - Me.Width - 400 ' LEFT / RIGHT OF SCREEN

End Sub

 

[Georgiboy]

Try your code as below, this will allow you to click the same find button again to find the next value:

Dim Rng As Range
Dim ws As Worksheet

Private Sub ClearSearchField_Click()
    TextBox1.Value = ""
    TextBox1.SetFocus
    Set Rng = ws.Range("A5")
End Sub

Private Sub CloseForm_Click()
    Unload DatabaseSearch
End Sub

Private Sub FindTheValue_Click()
    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 Sub

Private Sub TextBox1_Change()
    TextBox1 = UCase(TextBox1)
End Sub
Private Sub UserForm_Initialize()
    Set ws = Sheets("DATABASE") ' CHANGE SHEET NAME TO SUIT
    Set Rng = ws.Range("A5")
    Me.StartUpPosition = 0
    Me.Top = Application.Top + 135  ' MARGIN FROM TOP OF SCREEN
    Me.Left = Application.Left + Application.Width - Me.Width - 400 ' LEFT / RIGHT OF SCREEN
End Sub

 

[ipbr21054]

Thanks.

Can you advise,
I see you put the range as A5 as opposed what i put.
It works but just asking does that then just search from A5 as far across rows with text & same for down the page ?

I noticed that when searching has finished it starts from the start again,
example

Search found on row 1, 55, 200 then if your notching closely it loops 1, 55 , 200
What can be done so once it reaches the last found item so in the example row 200 we see a Msgbox
Otherwise some users will be there all day ha ha ha

Thanks

 

[ipbr21054]

I dont see what this is doing ?

Private Sub ClearSearchField_Click()
    TextBox1.Value = ""
    TextBox1.SetFocus
    Set Rng = ws.Range("A5")
End Sub

My code here just clears TextBox1 if a user say types a mistake so what is the purpose of the A5 part ?

 

[Georgiboy]

That part assumes a new search is being performed, it sets the Rng back to first cell of the search range.

As Rng is defined outside of the Sub in the method I have used, it will be remembered from the last search. I assumed you could make a new search without closing the form each time.

 

[ipbr21054]

OK thanks.
Can you advise that when the last value is found we then stop as opposed to starting the same over again.
I mean say when we get to last row that has values in may be ?

 

[Georgiboy]

A few things to note:
I have amended the search range to start in row 4 BUT it should not find anything in row 4 due to me using the 'FindAfter' range of 'ws.Range("AB4")'. As this is the last column in your search range, it will start it's search in range("A5") as this would be the next cell in the search range. This allows for the value to be found in the first cell of your search range (A5)

I have added the variable 'lRng', this will cater for the eventuality that only one value is found and will stop it looping the same cell over and over.

I have added the variable 'lr' to make sure that we are finding the next value and never the previous one.

Give it a try and see if it does what you need.

Dim rng As Range
Dim ws As Worksheet
Dim lr As Long
Dim lRng As String

Private Sub ClearSearchField_Click()
    TextBox1.Value = ""
    TextBox1.SetFocus
    Set rng = ws.Range("AB4")
    lr = 4
End Sub

Private Sub CloseForm_Click()
    Unload DatabaseSearch
End Sub

Private Sub FindTheValue_Click()
    Dim rRow As Long
    Dim rCol As Long
    Set rng = ws.Range("A4:AB3000").Find(Me.TextBox1.Value, rng) ' RANGE TO LOOK IN & TEXTBOX NAME FOR WHAT TO LOOK FOR
  
    If rng Is Nothing Then
        rRow = 0
    Else
        rRow = rng.Row
    End If
  
    If rng Is Nothing Then
        MsgBox "NOTHING TO MATCH OR LAST VALUE FOUND", vbCritical, "VALUE NOT FOUND MESSAGE"
        Exit Sub
    ElseIf Not rng Is Nothing And lr <= rRow And lRng <> rng.Address Then
        ws.Activate
        rng.Select
        lr = rng.Row
        lRng = rng.Address
    ElseIf rng Is Nothing Then
        MsgBox "NOTHING TO MATCH OR LAST VALUE FOUND", vbCritical, "VALUE NOT FOUND MESSAGE"
        Exit Sub
    Else
        MsgBox "LAST VALUE FOUND"
        Exit Sub
    End If
End Sub

Private Sub TextBox1_Change()
    TextBox1 = UCase(TextBox1)
End Sub
Private Sub UserForm_Initialize()
    Set ws = Sheets("DATABASE") ' CHANGE SHEET NAME TO SUIT
    Set rng = ws.Range("AB4")
    lr = 4
    Me.StartUpPosition = 0
    Me.Top = Application.Top + 135  ' MARGIN FROM TOP OF SCREEN
    Me.Left = Application.Left + Application.Width - Me.Width - 400 ' LEFT / RIGHT OF SCREEN
End Sub

 

[Georgiboy]

If you want the code to reset when the user changes the value in the textbox in the same way as if they use the clear button then you can change:

Private Sub TextBox1_Change()
    TextBox1 = UCase(TextBox1)
End Sub

To:

Private Sub TextBox1_Change()
    TextBox1 = UCase(TextBox1)
    Set rng = ws.Range("AB4")
    lr = 4
End Sub

 

[Georgiboy]

Looking at it now, I think I left an elseif in there by mistake, you can probably remove:

ElseIf rng Is Nothing Then
        MsgBox "NOTHING TO MATCH OR LAST VALUE FOUND", vbCritical, "VALUE NOT FOUND MESSAGE"
        Exit Sub