VBA - coding for a column sort based on form entry with multiple options
Hi All
I have created a database in excel that contains a list of materials with min and max widths, thicknesses, weight and quantities in it.
I have a search form that the users can type in the min and max amounts (restricted to numbers) that excludes rows accordingly to meet this criteria.
So far I can get it to work if they put a min OR a max in (leaving the other option blank)
what I am struggling with is how to add in the rule - if they select both a min and max
Everything I've tried so far doesnt work, but coding is by no means something I do very often or very much of.
Any help or suggestions would be greatly appreciated.
eg...
what I need to code is
Rule 1 Minimum requirement - if From text box has a value and To does not have a value - sort column AS <= using criteria set in From Text box
Rule 2 Maximum requirement - if To text box has a value and From does not have a value - sort column AS >= using criteria set in To Text box
Rule 3 - if From and to have a value - sort column AS by specified From value and column AT by specified To value.
What I currently have
'Quantity VALUES..
'selecting the min requirement
zQtyFrom = frmSearch.tbQtyFrom.Value 'fetch value from named textbox
If zQtyFrom <> "" Then 'value is not blank, so set filter..
[a10].AutoFilter Field:=[as10].Column, Criteria1:="<=" & zQtyFrom '<<< column [AS]
End If
' selecting the max requirement
zQtyTo = frmSearch.tbQtyTo.Value 'fetch value from named textbox
If zQtyTo <> "" Then 'value is not blank, so set filter..
[a10].AutoFilter Field:=[as10].Column, Criteria1:=">=" & zQtyTo '<<< column [As]
End If
when u set the 2nd filter, it
when u set the 2nd filter, it cancels the first one..
try recording a macro where you set a min and a max, and that will give u the syntax
Thanks Nick, will try this
Thanks Nick, will try this tomorrow as its been driving me crazy as I realised I need additional criteria in..
i.e if a min and max requirement is entered - when it sorts the minimum, it needs to refer to what was entered in the min range and do a greater or equal to... up to whatever the max range has been entered.
:/
Thank you for the suggestion.
Gemma
I think this is what you're looking for
If zQtyFrom = "" And zQtyTo = "" Then Exit Sub
If zQtyFrom <> "" And zQtyTo <> "" Then
[a10].AutoFilter Field:=[as10].Column, Criteria1:=">=" & zQtyFrom, Operator:=xlAnd, Criteria2:="<=" & zQtyTo
Else
If zQtyFrom <> "" Then 'value is not blank, so set filter..
[a10].AutoFilter Field:=[as10].Column, Criteria1:=">=" & zQtyFrom '<<< column [AS]
End If
If zQtyTo <> "" Then 'value is not blank, so set filter..
[a10].AutoFilter Field:=[as10].Column, Criteria1:="<=" & zQtyTo '<<< column [As]
End If
End If
Thank you Jon
Hi Jon
Thank you so much, I only needed to change the exit sub to and end if (as there are other possible criteria they could select - but you wouldnt have know that)
It now works :)
Have a lovely weekend.
Additional Info!
Glad it worked for you :)
Gemma, one thing you need to know (which I forgot to mention last night) and that is I switched the "<" and ">" in your original filter criteria as the logic did not seem to be correct
Hope this helps
- Jon