如何在VBA中使用.Range.Value粘贴时应用加权平均公式？

需求回顾 #

• 将「Past」工作表的动态数据区域复制到「Present」工作表
• 替换指定列的数据：
  • C列：用加权平均×数量替代原数量，加权平均取值为其他工作表的F7/F6（示例值为2）
  • D列：用加权平均×数量替代原数量，加权平均取值为其他工作表的G7/G6（示例值为1.5）

修正后的代码 #

Sub CopyAndCalculateData()
    Dim wsPast As Worksheet, wsPresent As Worksheet, wsOther As Worksheet
    Dim lastRow As Long
    Dim avgC As Double, avgD As Double
    
    ' 绑定工作表对象（替换"其他工作表名称"为实际表名）
    Set wsPast = ThisWorkbook.Worksheets("Past")
    Set wsPresent = ThisWorkbook.Worksheets("Present")
    Set wsOther = ThisWorkbook.Worksheets("其他工作表名称")
    
    ' 计算加权平均值
    avgC = wsOther.Range("F7").Value / wsOther.Range("F6").Value
    avgD = wsOther.Range("G7").Value / wsOther.Range("G6").Value
    
    ' 获取Past表数据最后一行（动态区域）
    lastRow = wsPast.Cells(wsPast.Rows.Count, "A").End(xlUp).Row
    
    ' 复制无需计算的A-B列
    wsPast.Range("A1:B" & lastRow).Copy wsPresent.Range("A1")
    
    ' 批量计算C列：加权平均×原数量
    wsPresent.Range("C1:C" & lastRow).Value = _
        wsEvaluate("=" & wsPast.Range("C1:C" & lastRow).Address(External:=True) & "*" & avgC)
    
    ' 批量计算D列：加权平均×原数量
    wsPresent.Range("D1:D" & lastRow).Value = _
        wsEvaluate("=" & wsPast.Range("D1:D" & lastRow).Address(External:=True) & "*" & avgD)
    
    ' 复制其余无需计算的列（可根据实际调整列范围）
    wsPast.Range("E1:XFD" & lastRow).Copy wsPresent.Range("E1")
End Sub

关键修正点说明 #

1. 规范加权平均计算：先提取其他工作表的数值完成除法运算，避免在赋值环节写入复杂表达式导致语法错误
2. 精准动态区域定位：用End(xlUp)定位数据最后一行，避免复制空白行
3. 批量运算优化：通过wsEvaluate实现整列批量计算，比循环单个单元格效率更高，且符合VBA数组运算语法
4. 跨表引用规范：用Address(External:=True)生成完整跨表引用路径，确保运算时能正确调用Past表的原始数据

原代码常见错误原因 #

• 直接在.Value中写入类似Range("C:C").Value = Range("C:C").Value * avgC的代码，VBA不支持这种直接的整列数组运算语法
• 未正确绑定其他工作表对象，导致加权平均值计算引用错误
• 动态区域判断逻辑不准确，复制了无效空白行

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