VB code to search according to data in search field.

[MSchädler]

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"]

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>

 

[MSchädler]

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

 

[AFPathfinder]

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.

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

 

[MSchädler]

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>}}

 

[AFPathfinder]

My fault. The function call syntax wasn't correct. Try this one:

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

 

[MSchädler]

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



​

 

[AFPathfinder]

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.

    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.

? 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.

 

[MSchädler]

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

 

[AFPathfinder]

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.

 

[MSchädler]

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