• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need Help To Debug Code

cyliyu

Member
I have the search button to carry out a search function from a spreadsheet and display the information based on the date range criteria stated.

I need help on my macro because when "Train Involve" used as search criteria, it won't work but if I changed to "S/No." the code work.
 

Attachments

  • TSearch.xlsm
    143.9 KB · Views: 5
Change the criteria to the yellow cells, note format
upload_2018-4-15_13-8-32.png

Change code to:
Code:
' Search PCE Serial Number Based on Inputs
Sub S_Search()

  Application.EnableEvents = False

    With Sheets("sTrain")
        Set critRng = [Criteria]
        Sheets("Train").Range("A5:T1050").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=critRng, CopyToRange:=.Range("E17:P1050"), Unique:=False
        .PivotTables("PivotTable1").PivotCache.Refresh
    End With
        Range("B17:P2000").Font.Name = "Calibri" 'set the font type and size
        Range("B17:P2000").Font.Size = 9
  Application.EnableEvents = True
   
    'With Sheets("sTrain")
    '    Range("E17: P200 ").Select 'this is the sort range"
    '    Selection.Sort Key1:=Range("E18"), Order1:=xlAscending, Header:=xlGuess, _
    '    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    'End With

End Sub




Enjoy
 
Thanks, Hui.

But I suppose to input the train number to be searched in column "C18", e.g 72221 and click on the search button so that only 72221 data will be displayed.

The code was working if I changed from train Involve to S/No. as per the attached file.

upload_2018-4-15_19-16-30.png

upload_2018-4-15_19-22-40.png
 

Attachments

  • S_No_Search.xlsm
    143.8 KB · Views: 1
In the attached:
1. Select whether you want to search by Train Involve or S/No. in cell C17 (there's a dropdown)
2. Type in as many values as you want (up to 200, whew!) or none (to return all of them within the date criteria) in the column below leaving no blanks.
3. Choose your dates.
4. Click the Search button.

Notes:
Entering nothing in cell C18 (the topmost cell) means that all trains will be returned.
I've changed column C of the Train sheet to Text format because I imagine that these numbers are identifiers and you'd never want to do arithmetic with them; this ensures that the wildcard function works with these train numbers as well as S/No.s.
 

Attachments

  • Chandoo38189TSearch.xlsm
    152.5 KB · Views: 5
Thanks p45cal for the option to have either Train or S/No. to perform the search.
I would like to have this option included in my worksheet.
Is there a way to get rid of the W1 to Y1 columns?

I am also curious to know what happen to the code, why I am able to search by S/No. and not Train number?
 
Is there a way to get rid of the W1 to Y1 columns?
Column W:Y are crucial for the advanced filter, but they can be elsewhere, even on a hidden sheet - as long as the code's told where they are.

I am also curious to know what happen to the code, why I am able to search by S/No. and not Train number?
I don't know. At least it works for both now.
 
Hi p45cal,
May I check with you the "pick from drop down list"?
Is it both the Train and S/No. must be next to each others?
Because my existing spreadsheet the train is at column "D" and S/No. is at column "F".
 
They don't have to be next to each other, but cell C17 must match exactly the column header you want to retrieve data from.
In the data validation, instead of:
=Train!$C$5:$D$5
you can have:
Train Involve,S/No.
Note the full stop at the end (since you have another header without one).
 
The error was due to missing " ' " in Train Involve.
Still trying to find out why when input a S/No. it came with " ' " whereas Train Involve without.
I did mention: "I've changed column C of the Train sheet to Text format because I imagine that these numbers are identifiers…" earlier.
 
Back
Top