Search and Filter Time

noufi

New Member
Joined
Dec 19, 2018
Messages
6
hi guys,

i need to search and filter time . it is searching text and numerical values. but when i search for time it is hiding the data.
below is my database and the code i used.

LINKCLINK
8:00:00er
GHJ45
SALIM123
SAIFDF
9:20:00657
10:20:0078989
10:35:00900

<colgroup><col style="text-align: center;"><col style="text-align: center;"></colgroup><tbody>
</tbody>


Type here to search

<tbody>
</tbody>
search button

<tbody>
</tbody>
clear button

<tbody>
</tbody>

below is the code i used:

Private Sub CommandButton1_Click()
Dim myButton As OptionButton
Dim SearchString As String
Dim ButtonName As String
Dim sht As Worksheet
Dim myField As Double
Dim DataRange As Range
Dim mySearch As Variant




Set sht = ActiveSheet


'Unfilter Data (if necessary)
On Error Resume Next
sht.ShowAllData
On Error GoTo 0

'Filtered Data Range (include column heading cells)
Set DataRange = sht.Range("A2:AP10000") 'Cell Range




'Retrieve User's Search Input
mySearch = sht.Shapes("UserSearch").TextFrame.Characters.Text 'Control Form



'Determine if user is searching for number or text
If IsNumeric(mySearch) = True Then
SearchString = "=" & mySearch
Else
SearchString = "=*" & mySearch & "*"
End If

'Loop Through Option Buttons


For Each myButton In sht.OptionButtons
If myButton.Value = 1 Then
ButtonName = myButton.Text
Exit For
End If

Next myButton

'Determine Filter Field
On Error GoTo HeadingNotFound
myField = Application.WorksheetFunction.Match(ButtonName, DataRange.Rows(1), 0)
On Error GoTo 0

'Filter Data
DataRange.AutoFilter _
Field:=myField, _
Criteria1:=SearchString, _
Operator:=xlAnd

'Clear Search Field
sht.Shapes("UserSearch").TextFrame.Characters.Text = "" 'Control Form

Exit Sub


'ERROR HANDLERS
HeadingNotFound:
MsgBox "The column heading [" & ButtonName & "] was not found in cells " & DataRange.Rows(1).Address & ". " & _
vbNewLine & "Please check for possible typos.", vbCritical, "Header Name Not Found!"
End Sub


Private Sub CommandButton2_Click()


'PURPOSE: Clear all filter rules


'Clear filters on ActiveSheet
On Error Resume Next
ActiveSheet.ShowAllData
On Error GoTo 0

End Sub

i need to search and filter time also with numericals and text.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Forum statistics

Threads
1,136,304
Messages
5,674,973
Members
419,537
Latest member
ucatchy

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
Top