VB code to search according to data in search field.

MSchädler

Board Regular
Joined
Apr 27, 2017
Messages
87
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
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>

 

AFPathfinder

Well-known Member
Joined
Apr 8, 2012
Messages
519
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)
 

MSchädler

Board Regular
Joined
Apr 27, 2017
Messages
87
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

Well-known Member
Joined
Apr 8, 2012
Messages
519
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
 

MSchädler

Board Regular
Joined
Apr 27, 2017
Messages
87
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

Board Regular
Joined
Apr 27, 2017
Messages
87
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>
 

AFPathfinder

Well-known Member
Joined
Apr 8, 2012
Messages
519
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
 

MSchädler

Board Regular
Joined
Apr 27, 2017
Messages
87
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

Well-known Member
Joined
Apr 8, 2012
Messages
519
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.
 

Forum statistics

Threads
1,081,545
Messages
5,359,433
Members
400,528
Latest member
Ratish52

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top