In Need of a Custom Search Box

[Buns1976]

Hi Everyone,

What would be the best way to use a cell in a worksheet as a search box to search column E:F for a UPC?
I would want to have that search box in Row 1 and freeze that row so its always visible.
The search box would be populated by a scan of a UPC. Once the UPC is found in E:F, I would want the cursor
to move to the cell which was found.

I realize this is much like the built in search function but the dialog box is always in the way so either have to
move it or close it.

Thanks so much.

 

[JLGWhiz]

You can try this change event code and see if it is what you want. The code should be installed in the sheet code module and it will run when there is a change is made in row 1.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim fn As Range
If Not Intersect(Target, Rows(1)) Is Nothing Then
    If Target <> "" Then
        Set fn = Intersect(ActiveSheet.UsedRange.Offset(1), ActiveSheet.Range("E:F")).Find(Target.Value, , xlValues)
        fn.Select
    End If
End If
End Sub

 

[Buns1976]

Hi JLGWhiz,

I am getting an error in the code at "fn.Select".

"Run-time error '91':
Object variable or With block variable not set"

FYI we will be using Cell K1 as the search.


Thank you!

 

[JLGWhiz]

If it did not find the value then it would not set the fn variable. The code below is modified to accomodate that condition and avoid the error message.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim fn As Range
If Not Intersect(Target, Rows(1)) Is Nothing Then
    If Target <> "" Then
        Set fn = Intersect(ActiveSheet.UsedRange.Offset(1), ActiveSheet.Range("E:F")).Find(Target.Value, , xlValues)
            If Not fn Is Nothing Then
                fn.Select
            Else
                MsgBox "Search Value Not Found!", vbInformation, "NO MATCH"
            End If
    End If
End If
End Sub