Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

search numeric values based on selected column from combobox



I  note  when  select the  column contains numeric values for  columns H,I,J  based on selected from combobox3 after select sheet name from combobox1  doesn't  show  data in listbox  if  I  search for  numeric values in columns  QT or UN or TOT

how  I  can  fix  this  problem?



Selected Answer

Hi Mussala

Your current code DOES refine ListBox1 entries for numeric values (in columns headed QT, UN or TOT) e.g choose to search SALES, (month) 6, UN and 95 - you will get three data rows.

Make it 955 and it goes to 1 row but 955. give no rows. That's because your numeric columns are formatted to display values like 955.00  but in fact they contain values like 955 (without the decimal separator) so the search *995.* has no matches. 

One way to fix that is by changing the filtering test, as shown in bold in the code extract below:

    ' filter on chosen column (Srch)
    ' but if searching in numeric columns, compare Data with same format as search
    If Month(Data(i, 2)) = cmb2 And _
        (Data(i, Srch) Like "*" & crit & "*" Or _
        Format$(Data(i, Srch), myFormat(1)) Like "*" & crit & "*") _
      x = x + 1
      For j = 1 To 10
        Temp(x, j) = Data(i, j)
        If j = 2 Then Temp(x, 2) = Format(Data(i, 2), "DD/MM/YYYY")
        If j >= 8 Then Temp(x, j) = Format$(Data(i, j), myFormat(1))
      Next j
    End If

I've done that in the attached revised file. Now you can type in values including the decimal separator and get appropriate results (for a text paertial match within numbers).

(Note: I launched the UserForm from VB Editor since your worksheet Search button is broken- possibly owing to an incorrect ref in Name Manager) 



Hi John ,

since your worksheet Search button is broken- possibly owing to an incorrect ref in Name Manager
actually  I  no  know  why  ?  !
this file is your attachment from the previous subject , also the same problem when you answered me in original subject and I download happens the same problem the button keep disabled. I  don't  interest  much for  this  problem  because  I  just take  the  whole codes and  put  in  my  project .
any way thanks  very  much  for  your  solution .
Mussala (rep: 12) Aug 23, '23 at 12:17 pm
Regarding "Search button is broken" - that's odd. My attachment to the previous question used the button from your question file (since I don't like or use buttons in the way you do).

Anyway I'm glad that my Answer helped- thanks for selecting it, Mussala.
John_Ru (rep: 6297) Aug 23, '23 at 12:39 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login