Help with Excel's AdvancedFilter: change criteria w/combobox?

[rlee16]

First, I must apologize for the code below, I am just starting out with VBA so I'm sure it will look very rudimentary to you all.

I'm a financial analyst making a user-friendly dialog box that will help navigate through my database, which basically includes 130 companies. I would like my user to be able to select certain criteria (e.g. 15 < PE multiple > 5, Market Cap>$100m) on a combobox and have my workbook pop out a new worksheet with the companies that fit those criteria.

Here are the challenges/questions I am facing:
1a) Criteria for AdvancedFilter: Currently, the AdvancedFilter is using a Range(&quot;Criteria&quot where Criteria is comprised of arbitrarily assigned variables. I would like to be able to adjust the criteria depending on what the user chooses in a dialog box (e.g. 5 variables all with different ranges). I'm sure there's a million ways to do this, but perhaps just being able to change the Range(&quot;Criteria&quot would be a simple solution (although I do not know how to do this). Is there a general way to name a range that can vary in size?

1b) In general, what is the best way to select cells whose range varies? Is it possible to count the number of filled cells (e.g. NumberofCompanies = 27), and then substitute that number into a Range(&quot;4,NumberofCompanies&quot?

2) When sorting the data, must I use a bubble-sort or the like? Is the sorting built-in function limiting in terms of changing the variables? If I know the total number of variables, why can I not plug that number into a Cell(C,1), select the whole thing and sort it?

Any kind of feedback would be appreciated. Please let me know if you need to see the whole file.

Regards,

Richard


Sub Search()

Worksheets(&quot;Sheet2&quot.Select
Cells.Clear
Worksheets(&quot;Rank&quot.Select
Call UsingAdvancedFilter ' Uses Advanced Filter function to search through the data

Range(&quot;B2Q140&quot.Copy ' Selecting results of search and pasting into new worksheet
Worksheets(&quot;Sheet2&quot.Select
Range(&quot;B4&quot.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Call FindNumCompanies
Call SortAfterSearch
End Sub

Sub UsingAdvancedFilter()
'Copied this sub from somewhere but I cannot remember where, I apologize for not citing source

Dim l_wkbTest As Workbook
Dim l_wksSheetToFilter As Worksheet

Set l_wkbTest = ThisWorkbook
Set l_wksSheetToFilter = l_wkbTest.Sheets(&quot;Rank&quot
l_wksSheetToFilter.Range(&quot;DBList&quot.AdvancedFilter xlFilterInPlace, Range(&quot;Criteria&quot, , False

Set l_wksSheetToFilter = Nothing
Set l_wkbTest = Nothing
End Sub


Sub FindNumCompanies()
' Determining number of companies found in search

NumCompanies = 0
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
NumCompanies = NumCompanies + 1
Loop
MsgBox &quot;Number of companies &quot; & NumCompanies
Worksheets(&quot;SearchOut&quot.Range(&quot;a1&quot.Value = NumCompanies

End Sub

Sub SortAfterSearch()

Rows(&quot;4:150&quot.Select
Selection.Sort Key1:=Range(&quot;AS4&quot, Order1:=xlDescending, Header:=xlNo _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range(&quot;B4&quot.Select
End Sub