1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

Discussion in 'VBA Macros' started by cyliyu, Apr 15, 2018.

  1. cyliyu

    cyliyu Member

    Messages:
    165
    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.

    Attached Files:

  2. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,440
    Change the criteria to the yellow cells, note format
    upload_2018-4-15_13-8-32.png

    Change code to:
    Code (vb):
    ' 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
    Chirag R Raval likes this.
  3. cyliyu

    cyliyu Member

    Messages:
    165
    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

    Attached Files:

  4. p45cal

    p45cal Well-Known Member

    Messages:
    1,115
    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.

    Attached Files:

    Chirag R Raval likes this.
  5. cyliyu

    cyliyu Member

    Messages:
    165
    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?
  6. p45cal

    p45cal Well-Known Member

    Messages:
    1,115
    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 don't know. At least it works for both now.
  7. cyliyu

    cyliyu Member

    Messages:
    165
    Noted with Thanks!
  8. cyliyu

    cyliyu Member

    Messages:
    165
    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".
  9. cyliyu

    cyliyu Member

    Messages:
    165
    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.
  10. p45cal

    p45cal Well-Known Member

    Messages:
    1,115
    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).
  11. p45cal

    p45cal Well-Known Member

    Messages:
    1,115
    I did mention: "I've changed column C of the Train sheet to Text format because I imagine that these numbers are identifiers…" earlier.
  12. cyliyu

    cyliyu Member

    Messages:
    165
    Thanks, p45cal.

Share This Page