VB code to search according to data in search field.

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>
MNOPQRSTU
ProcType
clip_image003.png
PID

<tbody>
</tbody>
DateUSPClusR-OTracLine
StatusTPPP000000121.10.20171151747ABCDE-FDEDiv.
TrueGPPP000000221.01.20171150453CDE-FFFDBN-GB
FalseDivP000000310.11.20151153699AIKG_JJU_HH450
FalseGPPP000000421.11.20171153860ZF-GGGHZ-TRVDiv.
Status RolloutTPPP000000521.11.20161154362WERT-KLHJDiv.
Status RolloutDivP000000601.05.20171154551BIU_JHZT-K20-30321
Status RolloutTPPP000000721.11.20171149739ABCDE-FDEDiv.
StatusTPPP000000810.11.20151153860CDE-FFFDDiv.
TrueGPPP000000921.11.20171154362AIKG_JJU_HHBN-GB50

<colgroup><col span="3"><col><col span="5"></colgroup><tbody>
</tbody>
<strike></strike>

 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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?
Code:
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)
 
Upvote 0
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.
 
Upvote 0
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.
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 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
 
Upvote 0
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:)
 
Upvote 0
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


<colgroup><col width="88" style="width:66pt"> </colgroup><tbody>
</tbody>
 
Upvote 0
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.
Code:
If Len(Suchfeld.Value) > 5 And Len(Suchfeld.Value) < 11 Then
    Suchfeld.Value = Replace(Suchfeld.Value, ".", "/")
End If
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

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