Wildcard with Cell Value

[adamsm]

Hi,

How could I change the code so that it searches the value from cell F2 instead of the value written in the input box.

Sub Extract_Data()
'Variables used by the macro
Dim FilterCriteria
Dim CurrentFileName As String
Dim NewFileName As String

'Get the current file's name
CurrentFileName = ActiveWorkbook.Name
'Select the first 10 columns and first 100 rows
'(note you can change this to meet your requirements)
On Error Resume Next
ActiveSheet.AutoFilterMode = False
On Error GoTo 0
'Apply Autofilter
Range("F10:I10").AutoFilter
'Get the filter's criteria from the user
FilterCriteria = InputBox("Enter Code")
If FilterCriteria = "" Then Exit Sub
FilterCriteria = Replace(FilterCriteria, "*", "")
FilterCriteria = "*" & FilterCriteria & "*"
'Filter the data based on the user's input
'NOTE - this filter is on column A (field:=1), to change
'to a different column you need to change the field number
Range("F10:I10").AutoFilter field:=1, Criteria1:=FilterCriteria
'Select the visible cells (the filtered data)
End Sub

Any help would be appreciated.

 

[SydneyGeek]

Try changing this...


FilterCriteria = InputBox("Enter Code")
If FilterCriteria = "" Then Exit Sub
FilterCriteria = Replace(FilterCriteria, "*", "")
FilterCriteria = "*" & FilterCriteria & "*"

to...

FilterCriteria = "*" & Range("F2").Value &"*"

Denis

 

[adamsm]

Thanks for the help. One more help if I may add. The following code filters the sheet with the column hedears text. But with this code the full text has to be entred to serach for the field.

For example to search Adam Shield the user has to type the full name instead of Adam.

Sub Search()
Dim n As Long
  Dim TgtCount As Long
    Application.ScreenUpdating = False
    If Sheets("CustomerList").FilterMode Then
        Sheets("CustomerList").ShowAllData
    End If
    If Range("F2") <> "" And Range("F3") <> "" Then
      Select Case Range("F2").Value
        Case "Customer ID"
          n = 3
        Case "Name"
          n = 4
        Case "Address"
          n = 5
          Case "Location"
          n = 9
      End Select
      TgtCount = Application.WorksheetFunction.CountIf(Sheets("CustomerList").Columns(n + 2), Range("F3").Value)
      If TgtCount = 0 Then
        MsgBox "The search string you entered does not exist", vbInformation
      Else
        Sheets("CustomerList").Range("C10").AutoFilter Field:=n, Criteria1:=Range("F3").Text
      End If
     
     
    End If
  
 Application.ScreenUpdating = True

End Sub

How can I combine the above code with the previous code to include the wild card character so that when the user types half of the search value in cell F3; the sheet gets filtered with the value.

In short how could I make the above code to search for the text by using the wild characters.

Note: Instead of F2 this code uses F3 to enter the search text. And in F2 to enter the search category. And the data starts from column C.

 

[SydneyGeek]

When building wild card filters for VBA, I cheat.
Turn on the recorder and create a filter using the Contains custom criteria. Once done, stop the recorder and you will have the syntax.

Denis