Excel宏问题：Consolidate按钮失效及常量值设置需求

问题根源 #

1. Clearcells宏的错误：使用Range.Clear会清除目标单元格的所有属性（公式、内容、格式），若D15:D181原本包含计算用公式，清除后会导致G183的总计公式失去有效引用，触发#REF!错误。
2. Consolidate宏的缺陷：未实现给H27设置$1的需求，且依赖Select/Activate的冗余写法，当G183本身为#REF!时，粘贴后目标单元格也会继承错误值。

修复后的完整代码 #

Option Explicit

Private Sub CommandButton1_Click()
    Dim wb As Workbook
    Dim wsData As Worksheet
    Dim wsDest As Worksheet
    Dim rDest As Range

    Set wb = ThisWorkbook ' 固定引用当前工作簿，比ActiveWorkbook更可靠
    Set wsData = wb.Worksheets("PRICE SCHEDULE")
    Set wsDest = wb.Worksheets("Requisition Form")
    
    ' 清空目标区域内容
    wsDest.Range("A27:H34").ClearContents
    
    Set rDest = wsDest.Cells(wsDest.Rows.Count, "G").End(xlUp).Offset(1)
    ' 确保起始位置从G27开始
    If rDest.Row < 27 Then Set rDest = wsDest.Range("G27")

    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    With wsData.Range("D14:F" & wsData.Cells(wsData.Rows.Count, "D").End(xlUp).Row)
        ' 无数据时直接退出
        If .Row < 14 Then GoTo CleanExit
        ' 过滤D列大于0的值
        .AutoFilter Field:=1, Criteria1:">0", Operator:=xlFilterValues
        ' 复制过滤后的D、F列数据
        Intersect(wsData.Range("D:D,F:F"), .Offset(1)).Copy
        rDest.PasteSpecial xlPasteValues
        ' 清除筛选
        .AutoFilter
    End With

CleanExit:
    With Application
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    ' 取消复制状态
    Application.CutCopyMode = False
End Sub

Sub Clearcells()
    ' 仅清除单元格内容，保留公式和格式
    ThisWorkbook.Worksheets("PRICE SCHEDULE").Range("D15:D181").ClearContents
End Sub

Sub Consolidate()
    Dim wsPrice As Worksheet
    Dim wsReq As Worksheet
    
    Set wsPrice = ThisWorkbook.Worksheets("PRICE SCHEDULE")
    Set wsReq = ThisWorkbook.Worksheets("Requisition Form")
    
    ' 直接赋值G183的值到G27，避免复制粘贴的冗余操作
    wsReq.Range("G27").Value = wsPrice.Range("G183").Value
    ' 设置H27为常量$1（若需数值格式1并显示为$1，可替换为注释的两行代码）
    wsReq.Range("H27").Value = "$1"
    ' wsReq.Range("H27").Value = 1
    ' wsReq.Range("H27").NumberFormat = "$#,##0.00"
End Sub

关键修改说明 #

• Clearcells宏：用ClearContents替代Clear，仅清除单元格内容，保留D列的公式和格式，确保G183的总计引用始终有效。
• Consolidate宏：
  • 移除冗余的Select/Activate操作，直接通过工作表对象引用单元格，提升代码效率和稳定性。
  • 新增H27单元格赋值逻辑，满足需求。
  • 直接用Value属性赋值，避免复制粘贴带来的错误传递问题。
• CommandButton1_Click宏：
  • 用ThisWorkbook替代ActiveWorkbook，避免切换工作簿时触发错误。
  • 修正起始行判断逻辑，确保数据从G27开始粘贴。
  • 添加Application.CutCopyMode = False，取消复制状态，避免残留剪贴板内容。

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