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.)
Hi
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?
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 & "*") _
Then
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 IfI'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)