如何在 Excel 的頁首或頁尾中尋找和替換？

在 Excel 中，尋找特定值並將其替換為另一個值是一項常見的任務，但標準的尋找和替換功能僅適用於選定區域、工作表和工作簿內，無法用於頁首和頁尾。在本文中，我將介紹一種宏代碼，用於在頁首和頁尾中尋找和替換值。

使用 VBA 在頁首或頁尾中尋找和替換

使用 VBA 在頁首或頁尾中尋找和替換

1. 激活您要在頁首和頁尾中尋找和替換文字的工作表，按下 Alt + F11 鍵以打開 Microsoft Visual Basic for Applications 視窗。

2. 點擊 插入 > 模組，然後將以下 VBA 代碼粘貼到模組中。參見截圖：

VBA：在頁首和頁尾中尋找和替換

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. 點擊 確定，接著會彈出另一個對話框讓您輸入要替換為的文字。參見截圖：

5. 點擊 確定。現在頁首和頁尾中的 “KTE” 已被替換為 “Kutools for Excel”。

提示：如果您想將此操作應用於整個工作簿，可以使用以下宏代碼。

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