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"

Sub Refresh_Listbox()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Data")

Dim dsh As Worksheet
Set dsh = ThisWorkbook.Sheets("Data_Display")

''''''''''' Copy Data ''''''''''
dsh.Cells.Clear
sh.AutoFilterMode = False


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

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

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

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


sh.UsedRange.Copy dsh.Range("A1")

sh.AutoFilterMode = False

Dim lr As Long

On Error Resume Next

lr = Application.WorksheetFunction.CountA(dsh.Range("A:A"))

If lr = 1 Then lr = 2



With Me.ListBox1
    .ColumnHeads = True
    .ColumnCount = 25
    .ColumnWidths = "35,33,30,100,70,44,60,120,120,120,120,70,100,50,70,70,70,70,200,100,10"
    .TextAlign = fmTextAlignCenter
    .RowSource = "Data_Display!A2:U" & lr

End With

lbl_count = ListBox1.ListCount - 1

Call sum_of_listbox_column

End Sub

this is how i populate my cmb_filter_by

Sub Refresh_DropDown_List()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("List")

Dim i As Integer

'''' Create list for Type of Issue
Me.cmb_factor.Clear
Me.cmb_factor.AddItem ""
For i = 2 To Application.WorksheetFunction.CountA(sh.Range("A:A"))
    Me.cmb_factor.AddItem sh.Range("A" & i).Value
Next i

'''' Create list for Machine
Me.cmb_machine.Clear
Me.cmb_machine.AddItem ""
For i = 2 To Application.WorksheetFunction.CountA(sh.Range("C:C"))
    Me.cmb_machine.AddItem sh.Range("C" & i).Value
Next i

'''''''''' Filter_by List
With Me.cmb_Filter_By
    .Clear
    .AddItem "ALL"
    .AddItem "Year" `<-- numeric only
    .AddItem "Week"
    .AddItem "Line"
    .AddItem "Machine"
    .AddItem "Description"
    .AddItem "Possible Cause"
    .AddItem "Corrective Action"
    .AddItem "Action to be taken"
    .AddItem "product"
    .AddItem "Factor"
    .AddItem "Status"
    .AddItem "Incharge"
    .AddItem "Note"
    .Value = "ALL"

End With

End sub

ty hope you can help me with this.

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

Quote from Carim

Have you tested :

Code

CLng(Me.cmb_Filter_By.Value)

i tried but the error is "miss match"

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

Quote from Carim

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

Display More

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.

Quote from Carim

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

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

Code

    .AddItem "ALL"
    .AddItem "Week"
    .AddItem "Line"
    .AddItem "Machine"
    .AddItem "Description"
    .AddItem "Possible Cause"
    .AddItem "Corrective Action"
    .AddItem "Action to be taken"
    .AddItem "Product"
    .AddItem "Factor"
    .AddItem "Status"
    .AddItem "Incharge"
    .AddItem "Note"

Display More

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

Sub Refresh_DropDown_List()



Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("List")



Dim i As Integer



'''' Create list for Type of Issue

Me.cmb_factor.Clear

Me.cmb_factor.AddItem ""

For i = 2 To Application.WorksheetFunction.CountA(sh.Range("A:A"))

Me.cmb_factor.AddItem sh.Range("A" & i).Value

Next i



'''' Create list for Machine

Me.cmb_machine.Clear

Me.cmb_machine.AddItem ""

For i = 2 To Application.WorksheetFunction.CountA(sh.Range("C:C"))

Me.cmb_machine.AddItem sh.Range("C" & i).Value

Next i



`Filter_by List

With Me.cmb_Filter_By

.Clear

.AddItem "ALL"

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

.AddItem "Week"

.AddItem "Line"

.AddItem "Machine"

.AddItem "Description"

.AddItem "Possible Cause"

.AddItem "Corrective Action"

.AddItem "Action to be taken"

.AddItem "product"

.AddItem "Factor"

.AddItem "Status"

.AddItem "Incharge"

.AddItem "Note"

.Value = "ALL"



End With



End sub

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.

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.

Quote from Carim

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 ?

Display More

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

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?

Quote from Carim

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

Display More

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

Quote from Carim

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

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

Quote from Carim

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