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