查找特定值,然后用Excel中的另一个值替换在我们的日常工作中很常见,但在Excel中,查找和替换功能在选择,工作表和工作簿中运行良好,但在页眉和页脚中找不到和替换。 在这篇文章中,我介绍一个宏代码来查找和替换页眉和页脚中的值。
导航窗格 - 查找和替换 |
Kutools for Excel先进的 查找和替换 功能,可以帮你找到和 替换多个工作表和工作簿中的值。 |
1。 启用要查找并替换页眉和页脚的工作表,按 Alt + F11键 键打开 Microsoft Visual Basic for Applications 窗口。
2。 点击 插页 > 模块,并将VBA代码粘贴到模块下面。 看截图:
VBA:查找并替换页眉和页脚
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | Sub FnR_HF() 'UpdateByExtendoffice20160623 Dim xStr As String , xRep As String 'Const csTITLE As String = "Find and Replace" On Error Resume Next xStr = Application.InputBox( "Replace what" , "Kutools for Excel" , , , , , 2) If xStr = "" Then Exit Sub xRep = Application.InputBox( "With what" , , "Kutools for Excel" , , , , , 2) With ActiveSheet.PageSetup ' Substitute Header/Footer values .LeftHeader = Application.WorksheetFunction.Substitute(.LeftHeader, xStr, xRep) .CenterHeader = Application.WorksheetFunction.Substitute(.CenterHeader, xStr, xRep) .RightHeader = Application.WorksheetFunction.Substitute(.RightHeader, xStr, xRep) .LeftFooter = Application.WorksheetFunction.Substitute(.LeftFooter, xStr, xRep) .CenterFooter = Application.WorksheetFunction.Substitute(.CenterFooter, xStr, xRep) .RightFooter = Application.WorksheetFunction.Substitute(.RightFooter, xStr, xRep) End With End Sub |
3。 然后按 F5 键运行代码,然后出现一个对话框,让您输入要查找的文本。 看截图:
4。 点击 OK,另一个对话框弹出,输入要替换的文本。 看截图:
5。 点击 OK。 现在的价值 “KTE” 已被替换 “Excel的Kutools” 在页眉和页脚。
小技巧:如果要为整个工作簿工作,可以使用下面的宏代码。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | Sub FnR_HF() 'UpdateByExtendoffice20171122 Dim I As Long Dim xStr As String , xRep As String 'Const csTITLE As String = "Find and Replace" On Error Resume Next xStr = Application.InputBox( "Replace what" , "Kutools for Excel" , , , , , 2) If xStr = "" Then Exit Sub xRep = Application.InputBox( "With what" , , "Kutools for Excel" , , , , , 2) For I = 1 To ActiveWorkbook.Sheets.Count With Sheets(I).PageSetup ' Substitute Header/Footer values .LeftHeader = Application.WorksheetFunction.Substitute(.LeftHeader, xStr, xRep) .CenterHeader = Application.WorksheetFunction.Substitute(.CenterHeader, xStr, xRep) .RightHeader = Application.WorksheetFunction.Substitute(.RightHeader, xStr, xRep) .LeftFooter = Application.WorksheetFunction.Substitute(.LeftFooter, xStr, xRep) .CenterFooter = Application.WorksheetFunction.Substitute(.CenterFooter, xStr, xRep) .RightFooter = Application.WorksheetFunction.Substitute(.RightFooter, xStr, xRep) End With Next End Sub |
联系客服