vba 实现excel下拉列表模糊查询模糊
时间: 2023-12-19 09:29:29 浏览: 88
通过VBA可以实现Excel下拉列表的模糊查询,具体实现方法如下:
1. 首先,在Excel中创建一个下拉列表,并将其命名为“ComboBox1”。
2. 在VBA中打开工作表,然后在“工具”菜单下选择“编辑”选项,进入VBA编辑器。
3. 在VBA编辑器中,选择“Sheet1”工作表,并在其中插入以下代码:
```
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim arr As Variant
Dim strTemp As String
Dim strList As String
Dim strResult As String
Dim intCount As Integer
If Target.Column = 1 And Target.Row > 1 Then
str = Target.Value
If str <> "" Then
arr = Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).Value
For i = 1 To UBound(arr)
strTemp = arr(i, 1)
If InStr(1, strTemp, str, vbTextCompare) > 0 Then
strList = strList & "," & strTemp
End If
Next i
If strList <> "" Then
arr = Split(strList, ",")
For i = 1 To UBound(arr)
For j = i + 1 To UBound(arr)
If arr(i) > arr(j) Then
strTemp = arr(i)
arr(i) = arr(j)
arr(j) = strTemp
End If
Next j
Next i
strResult = arr(1)
intCount = 1
For i = 2 To UBound(arr)
If arr(i) <> arr(i - 1) Then
intCount = intCount + 1
strResult = strResult & "," & arr(i)
End If
Next i
With ComboBox1
.Clear
.List = Split(strResult, ",")
.DropDown
End With
Else
ComboBox1.Clear
End If
Else
ComboBox1.Clear
End If
Else
ComboBox1.Clear
End If
End Sub
```
4. 保存并关闭VBA编辑器,然后返回Excel工作表。
5. 在单元格A2到A10中输入一些数据,然后选择单元格A1并打开下拉列表,输入一些字符进行模糊查询。
相关推荐
![mdb](https://img-home.csdnimg.cn/images/20210720083646.png)
![application/x-rar](https://img-home.csdnimg.cn/images/20210720083606.png)
![doc](https://img-home.csdnimg.cn/images/20210720083327.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)