I tried your code, I'm making it more complicated than it should be. Maybe you can help.

Thanks in advance.


Private Sub Search_Click()
Dim LR As Long, Val As String, cell As Range, Rng As Range, Code As Boolean
Dim dSh As Worksheet, v

'Setup
    Sheets("SEARCH BY ITEM CODE").Activate
    'First Search range
        If Range("C2") <> "" Then
            Val = Range("C2").Value & ""
            Code = True
        'ElseIf Range("E3") <> "" Then
            'Val = Range("E3").Value
            'Code = False
        Else
            MsgBox "Please enter ONE search criteria and try again."
            Exit Sub
        End If
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Set dSh = Sheets("DATABASE")
    Range("A9:G200").ClearContents

'Transfer matching data
    LR = dSh.Range("A" & Rows.Count).End(xlUp).Row
    dSh.Range("B2").AutoFilter
    
    If Code Then
        dSh.Range("B2").AutoFilter Field:=2, Criteria1:="=*" & Val & "*"
    Else
        dSh.Range("B2").AutoFilter Field:=3, Criteria1:="=*" & Val & "*"
    End If
    
    dSh.Range("B2:G" & LR).SpecialCells(xlCellTypeVisible).Copy Range("B9")
    dSh.Range("B2").AutoFilter