|
本帖最后由 一指禅62 于 2020-2-9 19:58 编辑
深受启发,感谢楼主!
对楼栋对应的房间号添加数据有效性,写了一段代码:
物业缴费系统.zip
(322.7 KB, 下载次数: 63)
- Public Sub 楼栋房号(ByVal key As String, ByVal Rng As Range)
- Dim arr, i%, n%, r%, a()
- If key = "" Then Exit Sub
- With Sheet3
- r = .Range("A65536").End(3).Row
- arr = .Range("A9:A" & r).Value
- End With
- For i = 1 To UBound(arr)
- If InStr(arr(i, 1), key) Then
- n = n + 1: ReDim Preserve a(1 To n)
- a(n) = Right(arr(i, 1), Len(arr(i, 1)) - Len(key))
- End If
- Next
- If n > 0 Then
- ActiveSheet.Unprotect
- With Rng.Validation
- .Delete
- .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
- xlBetween, Formula1:=Join(a, ",")
- End With
- Rng.Value = a(1)
- ActiveSheet.Protect
- End If
- End Sub
复制代码
|
|