本帖最后由 龙送农 于 2016-6-8 22:42 编辑
通过单元格D3选择单位,不用点击按钮,直接调出所要信息(里面的代码调出信息不准确)[size=14.6667px]。当D3选择单位是“全县”时,调出所有信息。
fjmxwrs老师的解答:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$2" Then
Dim arr, str1$, brr(), x%, i%, y%, r%
With Sheet1
r = .Range("B65536").End(xlUp).Row
arr = .Range("A5:AU" & r)
End With
str1 = Target.Value
If str1 = "全部" Then
For x = 1 To UBound(arr)
i = i + 1
ReDim Preserve brr(1 To 31, 1 To i)
brr(1, i) = i
For y = 2 To 6
brr(y, i) = arr(x, y)
Next y
For y = 8 To 11
brr(y - 1, i) = arr(x, y)
Next y
brr(11, i) = arr(x, 13)
brr(12, i) = arr(x, 15)
For y = 17 To 26
brr(y - 4, i) = arr(x, y)
Next y
For y = 30 To 37
brr(y - 7, i) = arr(x, y)
Next y
brr(31, i) = arr(x, 47)
Next x
Else
For x = 1 To UBound(arr)
If arr(x, 3) = str1 Then
i = i + 1
ReDim Preserve brr(1 To 31, 1 To i)
brr(1, i) = i
For y = 2 To 6
brr(y, i) = arr(x, y)
Next y
For y = 8 To 11
brr(y - 1, i) = arr(x, y)
Next y
brr(11, i) = arr(x, 13)
brr(12, i) = arr(x, 15)
For y = 17 To 26
brr(y - 4, i) = arr(x, y)
Next y
For y = 30 To 37
brr(y - 7, i) = arr(x, y)
Next y
brr(31, i) = arr(x, 47)
End If
Next x
End If
Application.EnableEvents = False
Application.ScreenUpdating = False
Range("A5:AE10000").ClearContents
Range("A5").Resize(UBound(brr, 2), UBound(brr)) = Application.Transpose(brr)
Erase arr, brr
Application.ScreenUpdating = True
Application.EnableEvents = True
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$D$2:$H$2" Then
Dim d As Object, arr, x%, str1$
Set d = CreateObject("scripting.dictionary")
With Sheet1
r = .Range("B65536").End(xlUp).Row
arr = .Range("A5:AU" & r)
End With
For x = 1 To UBound(arr)
d(arr(x, 3)) = ""
Next x
str1 = Join(d.keys, ",") & ",全部"
With Target.Validation
.Delete
.Add Type:=xlValidateList, Formula1:=str1
End With
d.RemoveAll
Erase arr
End If
End Sub
本帖最后由 02761752696 于 2016-6-10 14:39 编辑
龙送农 发表于 2016-6-8 19:39
老师:麻烦您帮再修改代码,如果一个单位人员名单在“数据库”不连续时,出现图片这样情况。
这是你的原代码的问题,之前没注意
|