Excel VBA实现估算表至发票表内容自动替换（Remove改Removed）

Option 1: Formula-Based Solution (Simple, for Specific Terms) #

If you only have a small set of verbs to convert, Excel’s SUBSTITUTE function is a quick fix. You can use it directly in your invoice sheet cells to pull data from the estimate sheet while swapping present-tense verbs to past tense.

For example, if your estimate sheet’s cell A1 has "Remove x", enter this in the corresponding invoice cell:

=SUBSTITUTE(estimate!A1, "Remove ", "Removed ")

To handle multiple verbs at once, chain additional SUBSTITUTE functions:

=SUBSTITUTE(SUBSTITUTE(estimate!A1, "Remove ", "Removed "), "Install ", "Installed ")

This will replace both "Remove " and "Install " with their past-tense versions in one formula.

Note: This works best for a fixed, short list of verbs. It’s easy to set up but gets messy if you need to add many more verbs later.

Option 2: VBA Solution (Scalable, for Multiple Verbs) #

For a more flexible, automated approach that updates the invoice sheet instantly when you edit the estimate sheet, use a VBA Worksheet Change event. Here’s how to set it up:

1. Open your workbook and press Alt + F11 to launch the VBA Editor.
2. In the left-hand Project Explorer, double-click the estimate sheet (under your workbook’s name).
3. Paste this code into the empty code window:

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Define which cells in the estimate sheet need syncing (adjust this to your actual range)
    Dim syncRange As Range
    Set syncRange = Me.Range("A1:C10") ' Example: cells A1 to C10 in the estimate sheet
    
    ' Only run the code if the edited cell is within our sync range
    If Not Intersect(Target, syncRange) Is Nothing Then
        Dim invoiceSheet As Worksheet
        Set invoiceSheet = ThisWorkbook.Worksheets("invoice")
        
        ' Loop through each cell that was changed
        Dim cell As Range
        For Each cell In Intersect(Target, syncRange)
            Dim originalText As String
            originalText = cell.Value
            
            ' Convert verbs to past tense—add more Replace lines for other verbs you need
            Dim modifiedText As String
            modifiedText = Replace(originalText, "Remove ", "Removed ")
            modifiedText = Replace(modifiedText, "Install ", "Installed ")
            modifiedText = Replace(modifiedText, "Repair ", "Repaired ")
            modifiedText = Replace(modifiedText, "Paint ", "Painted ")
            
            ' Write the updated text to the matching cell in the invoice sheet
            ' (adjust row/column references if your sync layout is different)
            invoiceSheet.Cells(cell.Row, cell.Column).Value = modifiedText
        Next cell
    End If
End Sub

Quick Customization Tips: #

• Update the Sync Range: Change Me.Range("A1:C10") to the actual range of cells in your estimate sheet that you want to sync.
• Add More Verbs: Copy and paste additional Replace(modifiedText, "Verb ", "Verbed ") lines for any other verbs you need to convert.
• Adjust Sync Mapping: If your invoice sheet uses a different layout (e.g., estimate cell A1 maps to invoice cell B2), replace invoiceSheet.Cells(cell.Row, cell.Column) with the correct reference (like invoiceSheet.Cells(cell.Row, cell.Column + 1) to shift one column right).

Important: If you previously used formulas to sync data, delete those formulas from the invoice sheet first—this VBA code writes directly to cells, and formulas would override its output.

Content of the question originates from Stack Exchange, asked by Arlo Dyer