Filter/Search by Year (Numeric not recognized)

  • MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

    filtering/searching for numeric VBA userform
    Hi I have a UserForm that has a filter Combobox that filters different criteria. and that includes "Year" and a TextBox which will be the search box. and a…
    www.mrexcel.com


    Hi, Good day


    so i have this problem,

    my search/filter box it is working fine if the value is with text or number, but i have a column that is only for designated for Year. which is numeric only.


    so in short my search box cannot find the or not work if the cell only contains numeric. but if it works if i added a letter on it ex. 2022 <- cannot be found 2023A <- searchable regardless if 23 or just A is typed in the box here is my code for filtering/search . i have 4 boxes for filtering.. this is also how i populate my listbox1

    Year column is on letter "T"






    this is how i populate my cmb_filter_by



    ty hope you can help me with this.

    Noob here, just want to learn more. I'm no expert nor a coder, I only learn by reading and watching youtube guides specially in this forum.

  • Hello,


    Not sure to totally understand your question ...


    If you need to convert your text "2023A" into a numeric... you can test

    Code
    CLng(Left("2023A", 4))

    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)


  • my column for year only contains number
    my filter boxes works fine for other condition except for "Year" which only contains number.
    so meaning i cannot search if i only type number unless i will add an alphabet




    what i want to happen is to encode only by number and also search it by numeric

    Noob here, just want to learn more. I'm no expert nor a coder, I only learn by reading and watching youtube guides specially in this forum.

  • Have you tested :

    Code
    CLng(Me.cmb_Filter_By.Value)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Have you tested :

    Code
    CLng(Me.cmb_Filter_By.Value)


    i tried but the error is "miss match"

    Noob here, just want to learn more. I'm no expert nor a coder, I only learn by reading and watching youtube guides specially in this forum.

    Edited 2 times, last by Carim: Password removed ().

  • i also tried and search how to filter the dates "from this date " and "up to this date" that's why theres a userform1 which i got from youtube and tried to modify it but can't make it work on my form

    Noob here, just want to learn more. I'm no expert nor a coder, I only learn by reading and watching youtube guides specially in this forum.

  • Thanks for your file :)


    You are obviously very familiar with you own work ... I am not ;)


    Where exactly is located your Sort By Year ???


    ... since the one search I found so far is using Row 1 Headers ....(...without the title Year...)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • thanks Carim i'm no expert nor a coder i only learn by reading and watching youtube guides specially in this forum.

    My sort by Year is with combobox "cmb_fiter_by" and the value of this combobox is pupulated my refresh dropdownlist.

    Noob here, just want to learn more. I'm no expert nor a coder, I only learn by reading and watching youtube guides specially in this forum.

  • First of all ... Many Congratulations on what you have already accomplished ...!!! :)


    Regarding the Me.cmb_Filter_By ... it is apparently populated as follows :


    .... WITHOUT YEAR ... !!! and IF you are Adding Year .... which Column would you like to refer to in the Data worksheet ....???

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • First of all ... Many Congratulations on what you have already accomplished ...!!! :)


    Regarding the Me.cmb_Filter_By ... it is apparently populated as follows :


    .... WITHOUT YEAR ... !!! and IF you are Adding Year .... which Column would you like to refer to in the Data worksheet ....???





    yes i did added the year, as i mention from the code above and also the "Year" column in located @ "T" how ever it does not recognize numeric or numbers when i filter it. unless i will put or add an alphabet together with the year.

    Images

    Noob here, just want to learn more. I'm no expert nor a coder, I only learn by reading and watching youtube guides specially in this forum.

    Edited once, last by bry02 ().

  • OK ... :)


    Only Row 51 :

    Code
    .AddItem "Year" `<-- numeric only Column is located in "Data" sheet "T"

    is not present in the test file you have attached ... ;)


    Now the problem lies with Column T which shows a full date and time ... i.e. a number with decimals ...


    As a consequence, in VBA, dealing with Dates and Times can be tricky ...


    To know exactly if you need DateSerial() or CInt() Or CLng(), could you give 3-4 examples of your possible inputs and your expected results (extracted from the T Column) ....

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Quote

    is not present in the test file you have attached ... ;) (Sorry about that i may have deleted it when trying to figure something)



    Quote


    To know exactly if you need DateSerial() or CInt() Or CLng(), could you give 3-4 examples of your possible inputs and your expected results (extracted from the T Column) ...


    ok here are the reason why i added this Year column


    1#. as you can see on the file there is a combo box for Week (W1 - W52) which is connected to a chart with formula and also to monitor the occurrence of specific issue, machine etc.

    2#. so right now is year 2022, and my boss does not want us to create or restart the file so we could also monitor the occurrences vs different year.
         - so what i see and notice that the "Week" will be redundant for example if we filter "W48" which from year 2023, those "W48" from year 2022 will also appear in the Listbox1


    thats why i come up with the idea of adding a Textbox for "year" so when we type the year in filter box it will only show the data from specific year.

    you will also notice from the file that there's a userform1 which i got from youtube i tried to modified it and get only the boxes for (show only "this date" up to this date") filter but got no luck so thats why i came up with the idea of adding this "year" column

    in short. i wanted to add a filter for date (from - to) or filter by year. so it wil be easy for us to see the result in listbox

    ----- additional example--------


    for example we wanted to see the status of this Line 1 ("L1") but only wanted to see during week 48 ("W48") in year 2022


    we will choose combo box value and type in filter/searchbox

    cmb_filter_by => Line / textbox => L1

    cmb_filter_by => Week / textbox => w48

    cmb_filter_by => Year / texbox = > 2022

    the list box will only show data from this criteria.

    Noob here, just want to learn more. I'm no expert nor a coder, I only learn by reading and watching youtube guides specially in this forum.

    Edited 2 times, last by bry02 ().

  • bry02

    Changed the title of the thread from “Filter/Search by Year (numeric)” to “Filter/Search by Year (numeric is not recognize)”.
  • In order to make your whole life easier ... and be totally coherent with all the work you have done ...

    why not adding, in your Data worksheet, a new column : Column U labelled Year ...


    In this Column U, you would have =YEAR(T2) copied all the way down ...


    Could this help ?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Hi Carim ty for keeping up to this thread.

    I actually did add new column with "Year" in Data sheet and also .additem "Year" for combobox but the problem is that the textbox for search is not working with "only numbers" it does not show the result even do i type the exact year ex. "2022". but it will work if i add a alphabet regardless of order. ex. 2022A , if i will search even just number "22" it will appear in the listbox

    Code
    why not adding, in your Data worksheet, a new column : Column U labelled Year ...

    does this mean that i need to remove the column update time?

    Noob here, just want to learn more. I'm no expert nor a coder, I only learn by reading and watching youtube guides specially in this forum.

    Edited once, last by bry02 ().

  • Let me Recap :


    1. You just said you have ALREADY Added in you macro: .AddItem "Year" ' <-- numeric only Column T is located in Data Sheet

    2. You have ALREADY added a Column U labelled Year in your Database ...


    - Both of these things DO NOT appear in your 'latest' test file...(the one you have attached...)

    - There is obviously no need to delete the full Update Date and Time in Column T

    - If, in your workbook, Year appears In Column U ... it is THE column you should filter ...and NOT column T as you mentioned ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • ok so i tried this method


    unfortunately, it does not work. :(


    but if im going to mix it with letter. it will work


    but what i wanted is to search it only by number

    Noob here, just want to learn more. I'm no expert nor a coder, I only learn by reading and watching youtube guides specially in this forum.

  • Should you need further assistance, do not hesitate the latest-latest workbook ... :) (the one WITH all your modifications)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Should you need further assistance, do not hesitate the latest-latest workbook ... :) (the one WITH all your modifications)

    hi Carim, i just got back to work UTC +3 KSA time, yes, i will upload it later. ty verry much

    Noob here, just want to learn more. I'm no expert nor a coder, I only learn by reading and watching youtube guides specially in this forum.

  • Hi Carim in response, here is my latest work book that i'm practicing, i already added the column for year and also added the year for combobox filter

    but again, i cannot search the year unless i will add a letter.


    i have practiced it with id # 3 and 4 in data


    try filter "Year" then type 22 = it will not show any result

    but if you filter it by "Year" then type 23 = it will show id #4 year 2023A its because it contains an alphabet (A)


    btw: same password

    Should you need further assistance, do not hesitate the latest-latest workbook ... :) (the one WITH all your modifications)

  • Carim

    Changed the title of the thread from “Filter/Search by Year (numeric is not recognize)” to “Filter/Search by Year (Numeric not recognized)”.
  • Hello,


    Below is your instruction to filter :

    Code
    'txt search 1
    If Me.cmb_Filter_By1.Value <> "ALL" Then
        sh.UsedRange.AutoFilter Application.WorksheetFunction.Match(Me.cmb_Filter_By1.Value, sh.Range("1:1"), 0), "*" & Me.txt_Search1.Value & "*"
    End If

    You are using as a criteria ( shown as the last element, after the coma ) : "*" & Me.txt_Search1.Value & "*"


    Your difficulty originates in the Asterix wildcard "*" added before and after your criteria ...


    The Asterix wildcard replaces one or more characters in a string


    If you were to remove them, and run your macro, filtering with the field Year and with the value 2022 ... everything works fine ... :)


    Hope this clarifies

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!