VBA - coding for a column sort based on form entry with multiple options

gemmz2902's picture

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

Nick's picture

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

gemmz2902's picture

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

gemmz2902's picture

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