PublicFunction ChooseFolder()AsString'定义函数,用于下面的调用'定义并新建一个对话框对象Dim dlgOpen As FileDialog
Set dlgOpen = Application.FileDialog(msoFileDialogFolderPicker)'如果当前没有对话框显示,就让他弹出对话框If dlgOpen.Show =-1Then ChooseFolder = dlgOpen.SelectedItems(1)Set dlgOpen =NothingEndFunctionSub 搜索确认()Dim Town AsStringDim wsh AsObjectCall 初始化 ' 初始化表格状态' Town = InputBox("请输入街道名称!") '街道输入
Town ="测试"For Each wsh In Sheets '表格循环
wsh.SelectCall 筛选(wsh, Town)Call 标记(wsh, Town)Next wsh
Excel.Worksheets(1).SelectEndSub
3.筛选
Sub 筛选(ByRef wsh AsObject, Town AsString)If wsh.Range("G1").Value ="乡(镇、街道)"Then'G列标题判定Call 筛选封装(wsh,"G1",7, Town)ElseIf wsh.Range("F2").Value ="乡"Then'F列标题判定-->相邻月和单月Call 筛选封装(wsh,"F2",6, Town)Else:'I列标题判定Call 筛选封装(wsh,"I2",9, Town)EndIfEndSub
Sub 筛选封装(ByRef wsh AsObject, rng AsString, fld AsByte, Town AsString)
wsh.Range(rng).AutoFilter field:=fld, Criteria1:=Town
EndSubSub 标记(ByRef wsh AsObject, Town)' wsh.ActivateDim a
Set a = Cells.Find(What:=Town)IfNot a IsNothingThen
wsh.Tab.ColorIndex =6ElseDebug.Print(ActiveWorkbook.Name&";"& wsh.Name&";找不到")EndIfEndSub
4.取消筛选标记
Sub 初始化()'取消筛选标记Dim wsh AsObjectFor Each wsh In Sheets
wsh.Tab.ColorIndex =-4142'取消颜色标记,取消筛选,取消隐藏
wsh.AutoFilterMode =False
Cells.EntireRow.Hidden =False
Cells.EntireColumn.Hidden =FalseNext wsh
EndSub