Creating a search function with bounds

[rharn]

Hello Everyone,

This is my first post as a member and I have frequented MrExcel quite often in the past week as my new research project requires a lot of VBA and I am very unfamiliar with the code.

I am trying to create a search function that can search for different compounds based off of properties and allow the user to specify the upper and lower limits of the properties for which they are searching for. As a result, I created a user form with 4 radio buttons that allow the user to specify if they want the value to be 'less than', 'equal', 'greater than', or 'between' a value that they specify in a textbox.

So if the user selects the 'less than' radio button the function is supposed to search for anything that is less than the user specified value from the database. I am currently unable to get my program to work and i think my if statements for the radio dials might be incorrect.

prop1option is the searched term that I am using in my find function. Any advice on the following string of code would be greatly appreciated!

Dim prop1option as Variant

       If ILsearch.P1B1.Value = True Then
            prop1option = (prop1option < ILsearch.TextBox1.Value)
        End If
        If ILsearch.P1B2.Value = True Then
            prop1option = (prop1option = ILsearch.TextBox1.Value)
        End If
        If ILsearch.P1B3.Value = True Then
            prop1option = (prop1option > ILsearch.TextBox1.Value)
        End If
        If ILsearch.P1b4.Value = True Then
            prop1option = (prop1option > ILsearch.TextBox1.Value) And (prop1option < ILsearch.TextBox2.Value)
        End If

 

[rharn]

Thank you so much for your help so far! and after looking at the code I understand better what you are trying to do. However, the userform will also have a drop down menu allowing the user to select which "property" they want to search for. And the range which the search function searches for will vary based off of the property they select. So in essence could this be accomplished by adding another if/then statement before the outer loop so that for example, if the user chooses 'Property A' then the Range(A) will be chosen, etc etc?

 

[Blammdon001]

Yes that is a very good idea, you could do that for the UserRange variable. You may also want to make sure that your text boxes only accept numerical values if that is what you are looking for, because if someone enters a letter on accident into the box it will not work correctly, this is just thinking ahead again. You'd be amazed by some of the things that people do, always assume that the user is an idiot and make your code as idiot proof as possible :D

 

[rharn]

Thanks so much Blammdon, I will work on this problem tomorrow in detail, your pseudo code gave me a really good idea on how to start the code but I am sure that questions will arise as I progress so please look out for more questions in the coming day from me! thanks in advance!

 

[Blammdon001]

No problem, glad to help!

 

[rharn]

Ok, so I've been able to successfully implement the structure you provided into my code and it worked wonderfully. But to make things slightly more challenging, I mentioned previously that the search function would allow the user to select up to 3 different properties using 3 comboboxes. So the way I was thinking about approaching this was to assign an array to each combobox, so if the user uses all searches using all 3 properties at once then each property search result will be put into an array and at the end all 3 arrays will be consolidated into 1 final array which will display the results.

As I have already proven to be dismal at vba I am clueless on how to set up an array and put search results into the array and to consolidate at the end. Since these will all be dynamic arrays, I do not know how specify an upper bound otherthan to count the number of rows there are in the database and specify that as an upperbound. My only concern with that approach is that will it be taking up unnecessary memory and slow down the overall search if i do this?

This is just part of one if/then function I have set up, would I be populating the array 'Results1()' correctly? I want to select the entire data entry into the array...

Dim FindRange1 as Range
Dim Find1 as Range
Dim Results1() as Variant
Dim R1 as Integer

    If ILsearch.Combox1.Enabled = True Then
        If ILsearch.ComboBox1.ListIndex = 0 Then
            Set FindRange1 = Worksheets("Properties").Range("P7:P535")
                For Each Find1 In FindRange1
                    If ILsearch.P1B1.Value = True Then
                        If (Find1.Value < TextBox1) Then
                            For R1 = 1 To UBound(Max)
                                Results1(R1) = Find1.EntireRow.Value
                            Next R1
                        End If
                    End If

So if the user chooses to uses combobox1 and select index option '0' the sub will search in the appropriate range correlated to the index option. On top of that, if the radio dial 'P1B1' is chosen, it will search the range for any values less than the value specified in Textbox1 from the userform 'ILsearch'. And the most embedded if/then function is my attempt at populating all of the data entries that the search finds into an array 'Results1()'

 

[rharn]

*Update

I've gone ahead and tried to write a code that will put the cell locations of all of the data entries found from the search function into an array but right now my code is not working, it is giving me a subscript out of range error. Please take a look and advise please. I am fairly certain hat I've either put my array loop in the wrong area or there is an error with my upper bound for my array:

Max = Range("E7:E1000").Cells.Count

If ILsearch.ComboBox1.Enabled = True Then
        If ILsearch.ComboBox1.ListIndex = 0 Then
            'Temperature range selection
            Set FindRange1 = Worksheets("Properties").Range("P7:P1000")
                If ILsearch.P1B1.Value = True Then
                    For R1 = 1 To Max
                        For Each Find1 In FindRange1
                            If (Find1.Value < TextBox1) And (Find1.Value > "0") Then
                                Results1(R1) = Find1.Address
                            End If
                        Next Find1
                    Next R1
                End If

Please keep in mind that this is only part of the code, the 'subscript out of range' error occurs in the "Results(R1) = Find1.Address' line.