VB code to search according to data in search field.

M

MSchädler

Board Regular
Joined
Apr 27, 2017
Messages
95
Hello, I have an excel sheet with several columns.Each column stands for specific historical data.
I would like to search in the excel sheetover a search field (command button).
A reset command button is planned toreset the sheet after every search and to empty the search field (commandbutton).
The search is limited to the columnsM to U.
All rows containing the searchcriteria have to be shown, all other rows must be hidden. The difficulty is todo a search for date, text and numbers or both.
Thanks for any help!
Marc


This shows my basic sheet set-up with some testdata.


<strike></strike>
[TABLE="width: 792"]
<colgroup><col span="3"><col><col span="5"></colgroup><tbody>[TR]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]T[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD]Proc[/TD]
[TD]Type[/TD]
[TD="align: left"]
clip_image003.png
PID

<tbody>
</tbody>
[/TD]
[TD]Date[/TD]
[TD]USP[/TD]
[TD]Clus[/TD]
[TD]R-O[/TD]
[TD]Trac[/TD]
[TD]Line[/TD]
[/TR]
[TR]
[TD]Status[/TD]
[TD]TPP[/TD]
[TD]P0000001[/TD]
[TD="align: right"]21.10.2017[/TD]
[TD="align: right"]1151747[/TD]
[TD][/TD]
[TD]ABCDE-FDE[/TD]
[TD]Div.[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]True[/TD]
[TD]GPP[/TD]
[TD]P0000002[/TD]
[TD="align: right"]21.01.2017[/TD]
[TD="align: right"]1150453[/TD]
[TD][/TD]
[TD]CDE-FFFD[/TD]
[TD]BN-GB[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]False[/TD]
[TD]Div[/TD]
[TD]P0000003[/TD]
[TD="align: right"]10.11.2015[/TD]
[TD="align: right"]1153699[/TD]
[TD][/TD]
[TD="colspan: 2"]AIKG_JJU_HH[/TD]
[TD="align: right"]450[/TD]
[/TR]
[TR]
[TD]False[/TD]
[TD]GPP[/TD]
[TD]P0000004[/TD]
[TD="align: right"]21.11.2017[/TD]
[TD="align: right"]1153860[/TD]
[TD][/TD]
[TD]ZF-GGGHZ-TRV[/TD]
[TD]Div.[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Status Rollout[/TD]
[TD]TPP[/TD]
[TD]P0000005[/TD]
[TD="align: right"]21.11.2016[/TD]
[TD="align: right"]1154362[/TD]
[TD][/TD]
[TD]WERT-KLHJ[/TD]
[TD]Div.[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Status Rollout[/TD]
[TD]Div[/TD]
[TD]P0000006[/TD]
[TD="align: right"]01.05.2017[/TD]
[TD="align: right"]1154551[/TD]
[TD][/TD]
[TD]BIU_JHZT-K[/TD]
[TD]20-30[/TD]
[TD="align: right"]321[/TD]
[/TR]
[TR]
[TD]Status Rollout[/TD]
[TD]TPP[/TD]
[TD]P0000007[/TD]
[TD="align: right"]21.11.2017[/TD]
[TD="align: right"]1149739[/TD]
[TD][/TD]
[TD]ABCDE-FDE[/TD]
[TD]Div.[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Status[/TD]
[TD]TPP[/TD]
[TD]P0000008[/TD]
[TD="align: right"]10.11.2015[/TD]
[TD="align: right"]1153860[/TD]
[TD][/TD]
[TD]CDE-FFFD[/TD]
[TD]Div.[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]True[/TD]
[TD]GPP[/TD]
[TD]P0000009[/TD]
[TD="align: right"]21.11.2017[/TD]
[TD="align: right"]1154362[/TD]
[TD][/TD]
[TD]AIKG_JJU_HH[/TD]
[TD]BN-GB[/TD]
[TD="align: right"]50[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>

 
Sort by date Sort by votes
Hello AFPathfinder,
Thanks for sharing your information. Just to keep things simple, here in Switzerland the date format is DD.MM.YYYY.
This is what has to be displayed in column P at start and at the end of any operation.

I could imagine the following scenario; when running the macro we change the search format from DD.MM.YYYY to the Number, change the format in column "P" from date to numbers as well, fill the searched number into "B2" to "J9", do the search and at the end reformat column "P" back to DD.MM.YYYY.
​
This might work. Just a thought.

I'm not in a real rush to get a solution. Lets keep at it, I'll try on my side as well and share if results are available.


Kind regards, Marc
 
Last edited:
Upvote 0

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
This may be a little over-engineered and probably a bit inefficient, but this should allow Excel to see it as a date. Give this a shot and see if it works.
Code: 
Private Sub CommandButton1_Click()
    Dim x As Integer
    Dim finalRow As Integer
    
    finalRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    If Sheets("Tabelle1").FilterMode = True Then
        Sheets("Tabelle1").ShowAllData
    End If
    
    If Len(Suchfeld.Value) > 5 And Len(Suchfeld.Value) < 11 And InStr(Suchfeld.Value, ".") > 0 Then
        Suchfeld.Value = StringToDate Suchfeld.Value
    End If
    
    If Suchfeld.Value <> "" And Not IsNumeric(Suchfeld.Value) And Not IsDate(Suchfeld.Value) Then
        For i = 2 To 10
            Cells(i, i).Value = "*" & Suchfeld.Value & "*"
        Next i
    Else
        For i = 2 To 10
            Cells(i, i).Value = Suchfeld.Value
        Next i
    End If
    
    Sheets("Tabelle1").Columns("M:U").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range _
        ("B1:J9"), Unique:=False
        
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    
    Range("M2").Select              'pointer to M2"
    
End Sub

Function StringToDate(strDate As String) As Date
'Converts a d.m.yy date into a Date object

    Dim iPeriod1 As Integer, iPeriod2 As Integer
    Dim iYear As Integer
    Dim iMonth As Integer
    Dim iDay As Integer
    
    iPeriod1 = InStr(strDate, ".")
    iPeriod2 = InStr(iPeriod1 + 1, strDate, ".")
    
    iDay = Left(strDate, iPeriod1 - 1)
    iMonth = Mid(strDate, iPeriod1 + 1, iPeriod2 - iPeriod1 - 1)
    iYear = Right(strDate, Len(strDate) - iPeriod2)
    
    StringToDate = DateSerial(iYear, iMonth, iDay)
End Function
 
Upvote 0
Hello AFPathfinder. Many thanks again for your support. I have entered your code into the file and I get a Syntaxerror at the line

If Len(Suchfeld.Value) > 5 AndLen(Suchfeld.Value) < 11 And InStr(Suchfeld.Value, ".") >0 Then
Suchfeld.Value= StringToDate Suchfeld.Value

Maybe you have an idea why?
I have the testfile under: https://www.dropbox.com/s/kut6zdwn77mu1p1/Test_SearchPlay6.xlsm?dl=0

Thanks for your idea? Marc<strike>
</strike>
 
Upvote 0
My fault. The function call syntax wasn't correct. Try this one:
Code: 
Private Sub CommandButton1_Click()
    Dim x As Integer
    Dim finalRow As Integer
    
    finalRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    If Sheets("Tabelle1").FilterMode = True Then
        Sheets("Tabelle1").ShowAllData
    End If
    
    If Len(Suchfeld.Value) > 5 And Len(Suchfeld.Value) < 11 And InStr(Suchfeld.Value, ".") > 0 Then
        Suchfeld.Value = StringToDate(Suchfeld.Value)
    End If
    
    If Suchfeld.Value <> "" And Not IsNumeric(Suchfeld.Value) And Not IsDate(Suchfeld.Value) Then
        For i = 2 To 10
            Cells(i, i).Value = "*" & Suchfeld.Value & "*"
        Next i
    Else
        For i = 2 To 10
            Cells(i, i).Value = Suchfeld.Value
        Next i
    End If
    
    Sheets("Tabelle1").Columns("M:U").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range _
        ("B1:J9"), Unique:=False
        
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    
    Range("M2").Select              'pointer to M2"
    
End Sub


Function StringToDate(strDate As String) As Date
'Converts a d.m.yy date into a Date object


    Dim iPeriod1 As Integer, iPeriod2 As Integer
    Dim iYear As Integer
    Dim iMonth As Integer
    Dim iDay As Integer
    
    iPeriod1 = InStr(strDate, ".")
    iPeriod2 = InStr(iPeriod1 + 1, strDate, ".")
    
    iDay = Left(strDate, iPeriod1 - 1)
    iMonth = Mid(strDate, iPeriod1 + 1, iPeriod2 - iPeriod1 - 1)
    iYear = Right(strDate, Len(strDate) - iPeriod2)
    
    StringToDate = DateSerial(iYear, iMonth, iDay)
End Function
 
Upvote 0
Good day AFPathfinder.
Many thanks again for your help.
I have entered your code into the sheet and did a filter search. Somehow it doesn't filter the date.
Here is the link to my drop box for sharing: https://www.dropbox.com/s/unkf0fh6kx5fb2k/Test_SearchPlay7.xlsm?dl=0

I did the stepping through the macro and saw that when going through the *Function stringtodate()" and then through the "if Suchfeld value..." it entered the format dd.mm.yyyy into "B2" to "J9" of the sheet.
Is this correct? Should it not convert into a number?
What do you think.

Appreciate your help, because I'm passed with my knowledge on this.
Marc



​
 
Upvote 0
It is a number, but it formats the number into a date, just like a cell does. I assume it would wrap the date as your operating system set region. I can set to the date/time of the column, but Excel will still search by the date format of my computer. The important thing is that Excel sees it as a date and not a string. If you want to pass it as a straight integer, you'll need to format the date to show as such. You'll want to change the format at the point of insert into the advanced filter area.
Code: 
    If Suchfeld.Value <> "" And Not IsNumeric(Suchfeld.Value) And Not IsDate(Suchfeld.Value) Then
        For i = 2 To 10
            Cells(i, i).Value = "*" & Suchfeld.Value & "*"
        Next i
[COLOR=#0000ff]    ElseIf IsDate(Suchfeld.Value) Then[/COLOR]
[COLOR=#0000ff]        For i = 2 To 10[/COLOR]
[COLOR=#0000ff]            Cells(i, i).Value = Format(Suchfeld.Value, "######")[/COLOR]
[COLOR=#0000ff]        Next i[/COLOR]
    Else
        For i = 2 To 10
            Cells(i, i).Value = Suchfeld.Value
        Next i
    End If

This passes the date value as an integer, but in my testing, it doesn't make a difference. However, it is filtering just fine for me. If you are having issues with it filtering, place a stop on the advanced filter line and use the immediate window to investigate the values it is trying to match.
Code: 
? IsDate(Range("P5").Value)
This tests the value in cell P5 to make sure it is seen by Excel as a date; returns True if it sees a date in that cell.
 
Upvote 0
Good evening,
it is 5pm here and I have to give you a very big thank you!!!
It works wonderfully in my test file too, and I'm amazed that it took this simple input into the vb code.

I will now do some testing in the life sheet and see how it behaves. To date we have 823 rows of data.

I'll allow myself to contact you again, if I have some issues with the live sheet.
Otherwise my sincere thanks for your very professional help.

Very kind regard.
Marc:)
 
Upvote 0
You're welcome! It was a nifty problem to solve and I can add the solution to my VBA toolbox. Let me know if you run into any issues.
 
Upvote 0
Good dayAFPathfinder
First, I wish you a HAPPY NEW YEAR.
Since our last contact I was able to work well with my Excel and your vb-code, Now I have the following question/request.
What line in the vb-code do I have to change in order to filter, not end ofmonth date but actual day date or older?
This would allow me to archive the lines with dates equal today or older.
Thanks for your appreciated inputs. Marc
 
Last edited:
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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
Back
Top