这不是一个问题,更像是一个解决方案,但我想在这里分享它,因为我在这里得到了所需的帮助。我想要通过工作表名称在活动工作簿中查找一个特定的Excel表格,并编写了以下代码来实现 "包含" 搜索。如果找到该表格,则会自动转到该表格,否则会询问用户是否存在多个匹配项:要随时结束搜索,请只需在输入框中输入空格即可。
Public Sub Find_Tab_Search() Dim sSearch As String sSearch = "" sSearch = InputBox("请输入搜索内容", "查找表格") If Trim(sSearch) = "" Then Exit Sub我希望这对大家有所帮助,也欢迎提出改进建议。
Dim sSheets() As String Dim sMatchMessage As String Dim iWorksheets As Integer Dim iCounter As Integer Dim iMatches As Integer Dim iMatch As Integer Dim sGet As String Dim sPrompt As String
iMatch = -1 iMatches = 0 sMatchMessage = ""
iWorksheets = Application.ActiveWorkbook.Sheets.Count ReDim sSheets(iWorksheets)
'将工作表名称放入数组中 For iCounter = 1 To iWorksheets sSheets(iCounter) = Application.ActiveWorkbook.Sheets(iCounter).Name If InStr(1, sSheets(iCounter), sSearch, vbTextCompare) > 0 Then iMatches = iMatches + 1 If iMatch = -1 Then iMatch = iCounter sMatchMessage = sMatchMessage + CStr(iCounter) + ": " + sSheets(iCounter) + vbCrLf End If Next iCounter Select Case iMatches Case 0 '没有匹配项 MsgBox "未找到与 " + sSearch + " 相关的表格" Case 1 '仅有一个匹配项,激活该表格 Application.ActiveWorkbook.Sheets(iMatch).Activate Case Else '有多个匹配项,询问用户应转到哪个工作表 sGet = -1 sPrompt = "找到多个匹配项。请选择需要查看的表格编号:" + vbCrLf + vbCrLf + sMatchMessage sPrompt = sPrompt + vbCrLf + vbCrLf + "输入空格以取消" sGet = InputBox(sPrompt, "请选择一个表格") If Trim(sGet) = "" Then Exit Sub sPrompt = "请输入数字" + vbCrLf + vbCrLf + sPrompt Do While IsNumeric(sGet) = False sGet = InputBox(sPrompt, "请选择一个表格") If Trim(sGet) = "" Then Exit Sub Loop iMatch = CInt(sGet) Application.ActiveWorkbook.Sheets(iMatch).Activate End Select
End Sub