Omit cell from search

kop442000

Board Regular
Joined
Jul 1, 2004
Messages
179
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:

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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Probably the easiest way would be instead of using Cells specify the range you actually want to search in, unless you actually need to search the entire sheet.

Such as:

Code:
Set c = Range("C3:C1000").Find(What:=Target.Value, LookIn:=xlValues)
 
Upvote 0
Sure,

Code:
Set c = Range("C3:C" & Range("C" & Rows.Count).End(xlUp).Row).Find(What:=Target.Value, LookIn:=xlValues)

or you could just go from C3 to the end of the column:

Code:
Set c = Range("C3:C" & Rows.Count).Find(What:=Target.Value, LookIn:=xlValues)

Not sure why the Boolean variable more though:

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

Note that as this sits though, it will continue to cycle through all the cells it finds in the range, it can be made to stop when it gets to the last one if you want.
 
Upvote 0
Hi there.

I am not great with VBA but when I put any of these codes in, the search box doesn't seem to work correctly - it just goes down the C column in turn.

Is it something I am doing wrong?
 
Upvote 0
Code:
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("C3:C" & Rows.Count)
        Set c = .Find(What:=Target.Value, LookIn:=xlValues)
        If c Is Nothing Then Exit Sub
        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

This parts I changed should find every occurrence of what you type in C2 in Column C from row 3 downward, but you do have to type something in C2.
 
Upvote 0
thank you that's great.

But I need my search to look through all columns not just C. Well not all.... I need to search from row 6 downwards.... and columns B through M.

Sorry to be a pain!
 
Upvote 0
Code:
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 Exit Sub
        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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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