VBA中WITH语句、IF语句与VLOOKUP公式的执行顺序问题

我帮你把这6个步骤整合到一套高效的VBA代码里，还解决你遇到的With语句和执行顺序问题——直接用下面的代码就能搞定所有需求：

Sub ProcessWorksheets()
    Dim wb_Final As Workbook, nameFinal As String
    Dim wb_Summary As Workbook, nameSummary As String
    Dim ws As Worksheet
    Dim targetRange As Range
    
    ' 打开目标工作簿（假设Final_Directory和Summary_Directory已提前定义路径）
    Set wb_Final = Workbooks.Open(Filename:=Final_Directory)
    nameFinal = wb_Final.Name
    
    Set wb_Summary = Workbooks.Open(Filename:=Summary_Directory)
    nameSummary = wb_Summary.Name
    
    ' 引用目标工作表，避免使用Activate/Select（原代码的"Sheets 1"疑似笔误，改为"Sheet 1"）
    Set ws = wb_Summary.Sheets("Sheet 1")
    
    With ws
        ' 步骤1：复制AT1:BZ1的公式到AT4:BZ4
        .Range("AT1:BZ1").Copy
        .Range("AT4:BZ4").PasteSpecial xlPasteFormulas ' 仅粘贴公式，更精准
        Application.CutCopyMode = False
        
        ' 步骤2-3：批量替换文本（用Range.Replace高效完成，无需循环）
        ' 替换35处"Last Weeks Summary Report Name"
        .Range("AT4:BZ4").Replace _
            What:="Last Weeks Summary Report Name", _
            Replacement:="This Weeks Summary Report Name", _
            LookAt:=xlPart, _
            MatchCase:=False
        
        ' 替换3处"Last Weeks Final Report Name"
        .Range("AT4:BZ4").Replace _
            What:="Last Weeks Final Report Name", _
            Replacement:="This Weeks Final Report Name", _
            LookAt:=xlPart, _
            MatchCase:=False
        
        ' 步骤4：移除单元格首字符的单引号
        ' 方法1：批量去除Excel自动添加的文本前缀单引号（高效）
        .Range("AT4:BZ4").Value = .Range("AT4:BZ4").Value
        
        ' 方法2：精准移除单个单元格的开头单引号（适合部分单元格有单引号的情况）
        ' Dim cell As Range
        ' For Each cell In .Range("AT4:BZ4").Cells
        '     If Left(cell.Value, 1) = "'" Then
        '         cell.Value = Mid(cell.Value, 2)
        '     End If
        ' Next cell
        
        ' 步骤5：将公式下拉至第5000行
        Set targetRange = .Range("AT4:BZ4").Resize(4997) ' 从第4行到第5000行共4997行
        .Range("AT4:BZ4").AutoFill Destination:=targetRange, Type:=xlFillDefault
        
        ' 步骤6：复制区域并粘贴为值压缩文件
        .Range("AT4:BZ5000").Copy
        .Range("AT4:BZ5000").PasteSpecial xlPasteValues
        Application.CutCopyMode = False
    End With
    
    ' 可选：保存并关闭工作簿
    wb_Summary.Save
    wb_Summary.Close
    wb_Final.Close
End Sub

• 打开工作簿：提前定义好路径后，直接通过代码打开目标文件，避免手动操作的误差。
• 步骤1复制公式：用xlPasteFormulas仅粘贴公式，比xlPasteAll更精准，不会附带多余格式。
• 步骤2-3批量替换：用Excel内置的Range.Replace方法一次性完成所有文本替换，比循环每个单元格效率高得多，自动匹配所有指定内容。
• 步骤4移除单引号：
  • 方法1适合批量处理：直接给单元格赋值Value = Value，可自动去除Excel为文本格式公式添加的单引号前缀。
  • 方法2适合精准处理：循环检查每个单元格的首字符，仅移除有单引号的单元格内容。
• 步骤5下拉公式：用AutoFill代替手动下拉，精准控制到第5000行，避免手动操作的遗漏。
• 步骤6粘贴为值：将公式区域转换为静态值，大幅减小文件体积，完成后清除复制模式。

1. With语句的正确用法：
   用With ws包裹所有工作表操作，无需反复写冗长的对象路径，同时避免Activate/Select，让代码更简洁、高效，减少运行错误。
2. 执行顺序优化：
   按照「复制公式→替换文本→移除单引号→下拉公式→粘贴为值」的顺序执行，确保每一步的修改都能正确传递到后续操作，比如先修改AT4:BZ4的公式，再下拉就能让所有行继承修改后的内容。
3. 替代循环的高效方案：
   用Excel内置的Replace和AutoFill方法代替VBA循环，处理大区域时速度提升明显，也减少代码复杂度。

内容的提问来源于stack exchange，提问作者iMILES