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

 

[Blammdon001]

Hi rharn!

I am assuming that "ILsearch" is the name of your user form and P1B"X" are your names for your radio buttons. If I am thinking about this correctly you want to make a search form that gives the user those four choices to search and compare the value "prop1option" to. To do this correctly i think you may want to add a "search" button and a text box to input your value for prop1option. Also, you should place your code in the event SearchButton Click() or whatever you decide to call your search button. This logic will allow the user to select a radio button and input a value into a text box and then upon clicking of the search button, the code will find all values that fit those specified conditions. Is this correct/ is this what you want to do?

Your logic for searching needs some work too, but we'll do this one step at a time.

 

[rharn]

Hi BlammDon,

The user form does have 2 text boxes if they want to indicate both an upper and lower bound and search button as well. I am currently writing this as a sub function that the search button will call when clicked upon. Otherwise everything else you assumed is correct, ILsearch is the name of the Userform and P1B'x' is the name of the buttons, prop1option is what is being compared to the database in excel for a certain property. I didn't include all of the details of my code since it is rather long. I just wanted to begin with this "section" of my code. The logic is the biggest issue I am having currently. I will include other parts of my code as the issues slowly get worked out here hopefully...!

 

[Blammdon001]

Ok well correct me if i am wrong, because i have only been using VBA for a couple months but i have an extensive knowledge of C# but, in your if statements you are setting prop1option equal to a value depending on which radio button is clicked..right? But the way you are doing it does not give prop1option a value, you are just making a comparison using the <,>,=,AND operators. Also, just thinking ahead...do you have any sort of code that only allows one radio button to be checked at once because the way your if statements are setup, there will be a problem if you select more than one item by accident, i would suggest using a drop down box instead of radio buttons to avoid this issue.

Edit: I was thinking about check boxes.....if you're using radio buttons or option buttons as they are called in excel then you should be fine haha

 

[rharn]

Ok well correct me if i am wrong, because i have only been using VBA for a couple months but i have an extensive knowledge of C# but, in your if statements you are setting prop1option equal to a value depending on which radio button is clicked..right? But the way you are doing it does not give prop1option a value, you are just making a comparison using the <,>,=,AND operators. Also, just thinking ahead...do you have any sort of code that only allows one radio button to be checked at once because the way your if statements are setup, there will be a problem if you select more than one item by accident, i would suggest using a drop down box instead of radio buttons to avoid this issue.

Edit: I was thinking about check boxes.....if you're using radio buttons or option buttons as they are called in excel then you should be fine haha

My intention is for prop1option to be a inequality? So if for example someone specifies "300" in the textbox and checks the less than radio button, i want to find all data entries that have a property of less than 300. Eventually I want to highlight the entire data entry and display the results in another form. I see what you are saying though, and I do not know how to correct this error in logic? This was my attempt at setting up a logic that would allow me to accomplish this task.

Is my explanation of the objective of my code and what prop1option is supposed to be any clearer? I can elaborate more if necessary. And you are correct about the radio buttons, only 1 can be selected at a time so there doesn't seem to be a problem with using them at the moment!

 

[Blammdon001]

My intention is for prop1option to be a inequality? So if for example someone specifies "300" in the textbox and checks the less than radio button, i want to find all data entries that have a property of less than 300. Eventually I want to highlight the entire data entry and display the results in another form. I see what you are saying though, and I do not know how to correct this error in logic? This was my attempt at setting up a logic that would allow me to accomplish this task.

As far as i know you cannot store an inequality in a variable, even if you are using the variant data type. A quick google search shows that the variant variable is unassigned and can assume any type of any of the other variables, when you give a value to a variant, excel chooses the best data type to represent it as and stored it as that. I am replicating your code in my own project and I am getting only boolean values when i do the inequalities. If you want inequalities you must write them out in whole, i dont think you can store them in a variant variable. But i don't know your the rest of your code maybe you are using these values later on but you must remember to set up your code to accept only true and false values for each inequality

What is the extent of your VBA knowledge? I would suggest looping through each value in your database and comparing it to the textbox values using an inequality statement and then storing it to an array or just pasting it in the other form depending on which radio button was clicked. Your way of first storing the True/False result of an inequality seems a little inefficient. But it may be the way you need to do it, like i said i dont know the rest of your code :D


Ex:

'Search for values less than Textbox1.Value in DataBase

Foreach DataBaseValue
{
If RadioButtonLessThan.Value= True Then
If DataBaseValue < Textbox1.value Then
'Add value to final form

End if
End if

}

 

[rharn]

I thought about doing that but the problem with a code like that is currently have 3 property variables that i can search for, which equates into 3 different ranges that the search function will search in. And with the option of 4 types of inequality I think the code will be rather long. AND on top of that eventually the search function will allow the user to search for all three properties at once, so it would be even longer as a result.

You are right though. That is why I posted my question on the forum, I realized how inefficient it was and I'm trying to find a way to express all of the options and search variables in a more efficient manner.

 

[Blammdon001]

Maybe im not understanding this completely but if you just loop through your whole list of values once, and check to see which radio button is checked and then you actually do the comparison using the inequality for each value then you will get what you are looking for. Using the loop and then each one of the inequalities you will be able to compare every value in your range using every possible comparison/inequality and then determine what to do if the value does satisfy the inequality. This only means you loop through a range once but check each value against 4 different inequalities. I thought your end goal was to paste data from a search using the inequalities as parameters. You can use this for each range defined by a property variable using a nested loop (a loop inside of another loop) This will do what you want right? :D

 

[rharn]

Okay, I THINK i understand what you are talking about in theory but I have no idea how to set up the code. Would it be possible to provide a rough structure so I can get an idea of how the structure should look and I will tinker with the details and variables after I understand how to set it up? Thanks in advance!

 

[Blammdon001]

This is more like pseudocode but I dont really have the time to write it out completely sorry! Just tell me if you have any questions about the logic of this, that is the main thing you should get out of my answers, after all learning VBA is best done through experimentation and trial/error. The hard part for this will be defining the user range dynamically and then accessing it. This code just loops through values in the range "UserRange" and checks to see if the radio button is checked and if the inequality is met and then moves or copies that value somewhere. Sorry if I can't be more help! Im sure one of the board frequents would be able and willing to write this out more completely

Private Sub SearchButton_Click()
Dim TextBox1 As Long
Dim TextBox2 As Long
Dim UserRange As Range
Dim Count As Long
Dim Count1 As Long
 
'Take in values for text boxes
TextBox1 = ILsearch.TextBox1.Value
TextBox2 = ILsearch.TextBox2.Value
'Specify UserRange here
UserRange = Range()
'In this test case it just selects column A starting a row 1 until it finds no more data (you will have to specify your range in a way that suits your needs)
'Outer Loop goes through each column
For Count1 = UserRange.StartColumn To LastColumn
 
    'Inner Loop goes through each row
    For Count = UserRange.StartRow To LastRow
        'If statements for each inequality
        'Checks to see if radio but is clicked to indicate selection
        If ILsearch.OptionButton1.Value = True Then
            If UserRange(Count, Count1).Value > TextBox1 Then
            'Transfer value to search results where ever that may be
            End If
        End If
 
 
    Next Count
 
Next Count1
End Sub