I have a Search function that I found over at The Spreadsheet Guru, which I have altered to suit my own needs, that works great for finding numbers and words, but it is unable to find dates. I was wondering if someone could please help me figure out how I can alter this code so that it can find dates?
On my spreadsheet I have an ActiveX combobox called DateUserSearch that displays all dates listed under the "Date" range. The search function then uses the date the user defines in the combobox as the search reference.
At the moment the search returns no results, even if the date the user has searched for exists within the range.
Thanks in advance,
Link1565
On my spreadsheet I have an ActiveX combobox called DateUserSearch that displays all dates listed under the "Date" range. The search function then uses the date the user defines in the combobox as the search reference.
At the moment the search returns no results, even if the date the user has searched for exists within the range.
Thanks in advance,
Link1565
Code:
Sub DateSearchBox()
'PURPOSE: Filter Data on User-Determined Column & Text/Numerical value
Dim myButton As OptionButton
Dim SearchString As String
Dim ColumnSearch As String
Dim sht As Worksheet
Dim myField As Long
Dim DataRange As Range
'Unfilter Data (if necessary)
On Error Resume Next
ActiveSheet.ShowAllData
On Error GoTo 0
'Filtered Data Range
Set DataRange = ActiveSheet.ListObjects("MainTable").Range
'Retrieve User's Search Input
mySearch = ActiveSheet.DateUserSearch.Text
'Determine if user is searching for number or text
If IsNumeric(mySearch) = True Then
SearchString = "=" & mySearch
Else
SearchString = "=*" & mySearch & "*"
End If
'The Column Being Searched
ColumnSearch = "Date"
'Determine Filter Field
myField = WorksheetFunction.Match(ColumnSearch, DataRange.Rows(1), 0)
'Filter Data
DataRange.AutoFilter _
Field:=myField, _
Criteria1:=SearchString, _
Operator:=xlAnd
'Clear search field
ActiveSheet.DateUserSearch.Text = ""
End Sub