mercredi 23 septembre 2015

Listbox multiple select conditions

I want to create a userform for the following code

Sub sanitising()
Dim i As Long, j As Long, EndRow As Long
Dim x As Variant, y As Variant, z As Variant, p As Variant

With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = False

EndRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
x = InputBox("Which column is your amount in?")
y = InputBox("Which column is your criteria?")
z = InputBox("which column is your second criteria?")
p = InputBox("which column is your third criteria?")

For i = EndRow To 4 Step -1
  If Range(x & i) <> "" Then
     For j = i - 1 To i - 3 Step -1
        If Range(x & j).Value = Range(x & i).Value * (-1) Then
           If Range(y & j) = Range(y & i) And Range(z & j) = Range(z & i) And Range(p & j) = Range(p & i) Then
               Rows(i).Delete
               Rows(j).Delete
           End If
        End If
     Next j
  End If 
Next i

 .Calculation = xlCalculationAutomatic

        .ScreenUpdating = True

End With
End Sub

I wanted to use a Listbox with all the column headers so that the users can just click and set the various columns as criteria. The source data may have many columns, but the users are free to select as many criteria as he wants. My difficulty is how to change the above code to work with the listbox. My code for listbox is

Dim header As Variant
columncount = ActiveSheet.UsedRange.Columns.count
header = Application.Transpose(ActiveSheet.Range("A1", Cells(1, columncount)))
UserForm2.ListBox1.List = header

I tried to store the selected items from the listbox into an array, and store their column number in another array:

 count = 0
 For n = 0 To ListBox1.ListCount - 1
     If ListBox1.Selected(n) = True Then
         ReDim Preserve criteria(count)
         criteria(count) = ListBox1.List(n)
         count = count + 1
     End If
 Next n

For m = 0 to CountA(criteria) 
ary(m) = application.match(criteria(m),header,false)

I'm wondering if you could just write one line that can loop through all the criteria first before moving on?

If I have to loop through each criterion, I tried to do the if CountA(criteria) = 1 Then ... Elseif CountA(criteria) = 2 Then ... Elseif CountA(criteria) = 3 Then ... and so on. But the code gets too long and I feel really dumb. I'm wondering whether there is better way to do this.

The problem is that this code is written to be used for users who do no have any VBA knowledge, so can't expect a user to change the VBA code according to the number of criteria each time.

Thank you so much! Greatly appreciate any help!

Aucun commentaire:

Enregistrer un commentaire