Omit cell from search

[kop442000]

Hi guys

I was very kindly given code on this forum which created a "Search Box" on my spreadsheet.

The one thing that I would like to tweak however if that it finds the word/number that has been inputted in the search box itself (as well as finding the other genuine entries). Is there a way that I can search the whole sheet apart from cell C2 (The search box cell)?

Here is the existing code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim response
Dim c As Range
Dim more As Boolean

If Target.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub

If Target.Column = 4 And Target.Row <> 4 Then
    Application.EnableEvents = False
    Target.Value = UCase(Target.Value)
    Application.EnableEvents = True
    Exit Sub
End If

more = True

If Target.Value = "t" Then
    Target.Value = Date
End If

If Target.Address = "$H$48" And Target.Value = "Ordered" Then
    Range("J48:K48").MergeCells = True
End If

If Target.Address = "$H$48" And Target.Value <> "Ordered" Then
    Range("J48:K48").MergeCells = False
End If

If Target.Address = "$C$2" Then
    Set c = Cells.Find(What:=Target.Value, LookIn:=xlValues)
    
    If c Is Nothing Then Exit Sub
    
    c.Select
    While more = True
        response = MsgBox("Find Next?", vbYesNo)
        If response = vbYes Then
            Set c = Cells.FindNext(c)
            c.Select
        Else
            more = False
        End If
    Wend
End If
End Sub

 

[kop442000]

Thank you for the code, that's great!

When someone seaches and nothing is found, at the moment the cell C6 becomes highlighted. Would it be possible for a box to say "No results found"?

Or even just go the next free row of the data?

Whichever is easiest really.

Thanks a million!

 

[Scott Huish]

I'm not sure how C6 would be highlighted (I'm guessing you mean Selected).
The default direction for the selection to move after hitting is Enter is down, it should go to C3 if it wasn't found.

This will show No Results Found if it doesn't find it. As far as going to the next free row of data, what column would this be based on?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim response
Dim c As Range

If Target.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub

If Target.Column = 4 And Target.Row <> 4 Then
    Application.EnableEvents = False
    Target.Value = UCase(Target.Value)
    Application.EnableEvents = True
    Exit Sub
End If

If Target.Value = "t" Then
    Target.Value = Date
End If

If Target.Address = "$H$48" And Target.Value = "Ordered" Then
    Range("J48:K48").MergeCells = True
End If

If Target.Address = "$H$48" And Target.Value <> "Ordered" Then
    Range("J48:K48").MergeCells = False
End If

If Target.Address = "$C$2" Then
    With Range("B6:M" & Rows.Count)
        Set c = .Find(After:=Range("M" & Rows.Count), What:=Target.Value, LookIn:=xlValues)
        If c Is Nothing Then
            MsgBox "No Results Found"
            Exit Sub
        End If
        Do
            c.Select
            response = MsgBox("Find Next?", vbYesNo)
            If response = vbNo Then Exit Do
            Set c = .FindNext(c)
        Loop
    End With
End If
End Sub

 

[kop442000]

I think C6 becomes selected because the cells before it are locked and unselectable, so it jumps to the first data cell.

The box you have created that says no results found is perfect thank you.

When I mentioned about it going to the next free row if there were no results found, I meant for it to go to column B next free row - kind of ready to add data.... the only problem with it going to C6 you see is that if you were some way down the data it scrolls the screen back up to the top (to C6) - and you have to scroll all the way down again if you want to go back to where you were before the search.

In fact, if the selected cell just stayed as the search box C2 when no data is found that would work well because that line is a frozen pane, and so there would be no scrolling.

Hope this is clear cos I know it is hard to understand when you can't see the sheet.

In summary I think if the selected box stayed as C2 in the event of "no results found" then my spreadsheet would be complete!

Thank you very much for your time.
kop44.

 

[Scott Huish]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim response
Dim c As Range

If Target.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub

If Target.Column = 4 And Target.Row <> 4 Then
    Application.EnableEvents = False
    Target.Value = UCase(Target.Value)
    Application.EnableEvents = True
    Exit Sub
End If

If Target.Value = "t" Then
    Target.Value = Date
End If

If Target.Address = "$H$48" And Target.Value = "Ordered" Then
    Range("J48:K48").MergeCells = True
End If

If Target.Address = "$H$48" And Target.Value <> "Ordered" Then
    Range("J48:K48").MergeCells = False
End If

If Target.Address = "$C$2" Then
    With Range("B6:M" & Rows.Count)
        Set c = .Find(After:=Range("M" & Rows.Count), What:=Target.Value, LookIn:=xlValues)
        If c Is Nothing Then
            Range("C2").Select
            MsgBox "No Results Found"
            Exit Sub
        End If
        Do
            c.Select
            response = MsgBox("Find Next?", vbYesNo)
            If response = vbNo Then Exit Do
            Set c = .FindNext(c)
        Loop
    End With
End If
End Sub

 

[kop442000]

That's perfect thank you.

You've been a great help and I really appreciate your time.

Cheers,
kop44.

 

[kop442000]

Oh no I'm sorry I spoke too soon!

To explain -my rows 1 to 5 are frozen. The rest below it can scroll....

But for some reason if I am scrolled right down, and then do a search which yields no results, it "resets" the scolling bit back to the top.
I thought that this was happening because the selected bit was selecting a cell near the top, but it stays in the frozen C2 search cell, so I don't know why it is doing it.

It is just that the user has to scrooooll back to the bottom to get to the newer data.

Any ideas?

Sorry

 

[kop442000]

Thinking about it - if it is a pain to rectify this, it's only really a tiny problem, so it doesn't really matter.

I am more than happy with how it works now.

 

[Scott Huish]

I thought that this was happening because the selected bit was selecting a cell near the top, but it stays in the frozen C2 search cell, so I don't know why it is doing it.

It's doing it because that's what you asked for:

In summary I think if the selected box stayed as C2 in the event of "no results found" then my spreadsheet would be complete!

Is this what you want?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim response
Dim c As Range

If Target.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub

If Target.Column = 4 And Target.Row <> 4 Then
    Application.EnableEvents = False
    Target.Value = UCase(Target.Value)
    Application.EnableEvents = True
    Exit Sub
End If

If Target.Value = "t" Then
    Target.Value = Date
End If

If Target.Address = "$H$48" And Target.Value = "Ordered" Then
    Range("J48:K48").MergeCells = True
End If

If Target.Address = "$H$48" And Target.Value <> "Ordered" Then
    Range("J48:K48").MergeCells = False
End If

If Target.Address = "$C$2" Then
    With Range("B6:M" & Rows.Count)
        Set c = .Find(After:=Range("M" & Rows.Count), What:=Target.Value, LookIn:=xlValues)
        If c Is Nothing Then
            Range("B" & Rows.Count).End(xlUp).Offset(1).Select
            MsgBox "No Results Found"
            Exit Sub
        End If
        Do
            c.Select
            response = MsgBox("Find Next?", vbYesNo)
            If response = vbNo Then Exit Do
            Set c = .FindNext(c)
        Loop
    End With
End If
End Sub

 

[kop442000]

I was happy with it staying in cell C2 but I didn't think that this would make it scroll back up to the beginning....

But how you have done it now is great. It is just as I want it now thank you very much for your help!

Kop44.