作者:乔山办公网日期:
返回目录:excel表格制作
VBA字典技术
1、建立辅助列,用公式:度=IF(A2="Eton",1,IF(A2="Apeal",2,IF(A2="Optima",3,4))) ,下拉公式:
2、自定义排序:开始-问-排序和筛选--自定义排序,主关键字答为“辅助列”,版升序;次关键字为“编号”列权,升序。
vba有个优点,就是可以录制宏
所以楼主的这个排序,完全可以录制一段排序的宏
然后根据录制的结果再进一步处理即可
所有代码如下:
Private Sub ComboBox1_Change() '第一个组合框变化e799bee5baa6e997aee7ad94e59b9ee7ad94364
Dim dc As Object
Set dc = CreateObject("Scripting.Dictionary")
Dim i As Long
ComboBox2.Clear
With Sheet1
For i = 1 To .[a65536].End(3).Row
If .Cells(i, 1) = ComboBox1.Value Then
If Not dc.exists(.Cells(i, 2).Value) Then
ComboBox2.AddItem .Cells(i, 2).Value
dc.Add Sheet1.Cells(i, 2).Value, i
End If
End If
Next
End With
ComboBox2.Value = ComboBox2.List(0)
End Sub
Private Sub UserForm_Initialize() '窗体初始化
Dim dc As Object
Set dc = CreateObject("Scripting.Dictionary")
Dim i As Long
For i = 1 To Sheet1.[a65536].End(3).Row
If Not dc.exists(Sheet1.Cells(i, 1).Value) Then
ComboBox1.AddItem Sheet1.Cells(i, 1).Value
dc.Add Sheet1.Cells(i, 1).Value, i
End If
Next
ComboBox1.Value = Sheet1.Cells(1, 1).Value
End Sub
Private Sub CommandButton1_Click() '排序按钮
Dim arr, brr(), crr()
arr = Sheet1.Range("A1:B" & Sheet1.[a65536].End(3).Row).Value
Dim i As Long, m As Long, n As Long
For i = 1 To UBound(arr)
If arr(i, 1) & arr(i, 2) = ComboBox1.Value & ComboBox2.Value Then
n = n + 1
ReDim Preserve brr(1 To 2, 1 To n)
brr(1, n) = arr(i, 1)
brr(2, n) = arr(i, 2)
Else
m = m + 1
ReDim Preserve crr(1 To 2, 1 To m)
crr(1, m) = arr(i, 1)
crr(2, m) = arr(i, 2)
End If
Next
With Sheet1
.Cells(1, "D").Resize(n, 2) = WorksheetFunction.Transpose(brr)
.Cells(n + 1, "D").Resize(m, 2) = WorksheetFunction.Transpose(crr)
End With
End Sub
详见附件: