查找特定值,然后用Excel中的另一个值替换在我们的日常工作中很常见,但在Excel中,查找和替换功能在选择,工作表和工作簿中运行良好,但在页眉和页脚中找不到和替换。 在这篇文章中,我介绍一个宏代码来查找和替换页眉和页脚中的值。
|
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 |
|
来自: 昵称65017511 > 《待分类》