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>

 

[AFPathfinder]

Re: Help needed with macro (VB code) to search according to data in search field.

Have you tried using the Find function? If the variable for the input box is set to Variant, does it search all data types?

strSearch = InputBox("Enter a search value.", vbOKCancel)

Set x = Sheets("Data").Range("M" & finalRow & ":U" & finalRow).Find(What:=strSearch, After:=Cells(1, 1), _
    LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=True)

 

[MSchädler]

Re: Help needed with macro (VB code) to search according to data in search field.

Hello AFPathfinder
Thank you for your proposal. Unfortunately it does not work in my test sheet or for what I'm looking for.
To show you what I would like and unfortunately it works for text but not for numbers or date. I have given you a link to my drop box to this test file. Maybe you can help me in finding why it doesn't filter numbers or date.
Link:https://www.dropbox.com/s/xch0liyu6m3h5k6/Test_Search.xlsm?dl=0

I have also prepared a test file with YOUR vba code and when executing I get a debug error message.
Link: https://www.dropbox.com/s/4o10ifyox8fhp97/Test_Search3.xlsm?dl=0

What I'm looking for is a macro that allows, after entering the search text, to show only the rows which contain that search value. All others rows are to be hidden. With the reset you show all data again.

​

Maybe you can help me out with an other idea.
Thanks in advance for your support.

 

[AFPathfinder]

Re: Help needed with macro (VB code) to search according to data in search field.

After looking at it, I'll say that is a nifty idea using advanced filter to search multiple columns. The issues were adding in the asterisks on both ends of the search value. I just used an If statement to switch the asterisks on if the value isn't a number or date. It's working good for me, but give it a shot and see if it does good for you.

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

 

[MSchädler]

Re: Help needed with macro (VB code) to search according to data in search field.

Hello AFPathfinder
Wow, thanks it works great for me too. So, my problem was the definition of text and numbers.

Again thanks and have a great day.
Marc

 

[MSchädler]

Re: Help needed with macro (VB code) to search according to data in search field.

Hello Pathfinder, after doing some extensif tests I have seen, that the "date = 21.11.2017" is not filtered. Do you know why? Thanks for clearing that. Marc[TABLE="width: 88"]
<colgroup><col width="88" style="width:66pt"> </colgroup><tbody>[TR]
[TD="width: 88, bgcolor: transparent, align: right"]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[MSchädler]

Re: Help needed with macro (VB code) to search according to data in search field.

To share my findings I'm sending you the link to the test file again. Link:https://www.dropbox.com/s/c45jntcs1ujdmz6/Test_Search4.xlsm?dl=0
I think the format of "date" is special and therefore not recognized.
Thanks for your support.
Marc

 

[AFPathfinder]

Re: Help needed with macro (VB code) to search according to data in search field.

If using periods instead of the slash, the "IsDate()" function reads it as text. You could insert a conversion section right below the Application False section to change the periods to slashes given a certain string length, but that may have unintended results.

If Len(Suchfeld.Value) > 5 And Len(Suchfeld.Value) < 11 Then
    Suchfeld.Value = Replace(Suchfeld.Value, ".", "/")
End If

 

[MSchädler]

Re: Help needed with macro (VB code) to search according to data in search field.

Hi AFPathfinder
Your idea is not bad and I tried to insert it in my code. Unfortunately it somehow does not filter.

My assumption is the following:
We're entering for example a date format 21.11.2017 which is actually the number 43060 (if you did not format for date) for search.
With your code we only change the format from 21.11.2017 to 21/11/2017 which is still a text.

I believe the macro should change (in background) the format in column "P" to read the number 43060, then also change the search field "B2 to J9" as number 43060 and at the end put everything back to initial format. What would be the code to do this?
I have prepared again the test file with the additional code in it: https://www.dropbox.com/s/vu4has8n8wbhh5d/Test_Search5.xlsm?dl=0

Thanks again for your help.
Marc

 

[AFPathfinder]

Re: Help needed with macro (VB code) to search according to data in search field.

It could be done that way, but the conversion still means that Excel needs to read it as a date before it can convert formats (or else the user would need to know the date serial to search effectively). The dates are listed in the m/d/yyyy format in the table, right (I'm not at my computer to see the files you shared)? If the dates are formatted in m/d/yyyy, but the user would search for a date using the d/m/yyyy, that might be a larger problem to solve. I'll have to do some research on that solution.