将一张纸上的最后一行相乘并放置到另一张纸上 [英] Multiplying Last Row from One Sheet and Placing Onto Other Sheet

问题描述

尝试从我的名为"DATA"的工作表中将2列的最后3行"production_units"乘以"production_cost"，并将它们放置在名为"Report"的工作表中，标题为生产成本".我一直在尝试，但无济于事.谢谢.

Trying to multiply 2 column's last 3 rows of "production_units" by "production_cost" by each other from my sheet called "DATA" and placing them in a sheet called "Report" under the column title "Production Cost". I've been trying but to no avail. Thanks.

我的电子表格如下:

我的代码:

Sheets("DATA").Activate
' Use this lRow now since the previous one require you to be in a With   loop
Range(Cells(lRow - 2, 1), Cells(lRow, 1)).Copy

With Sheets("Report")
.Activate
' Pastes the last 3 cells of Column A into the Month column
.Range("B9").PasteSpecial Paste:=xlPasteAll
.Range("B8").Formula = "Month"
.Range("C8").Formula = "Production Cost"
' Calculates the Production cost
.Range(.Cells(lRow - 3, 2), .Cells(lRow, 2)).Copy
.Range("C9").AutoFill Destination:=Range("C9:C11")
' Calculates the Inventory cost
.Range("C14").Select
.Range("D8").Formula = "Inventory Cost"
.Range("E8").Formula = "Total Cost"
.Range("B12").Formula = "Total"
End With
End Sub

推荐答案

这是您的代码的有效版本.您可能想看看语法.

Here is a working version of your code. You may like to look at the syntax.

    Dim WsData As Worksheet
Dim WsReport As Worksheet
Dim Rl As Long                          ' last row
Dim Rng As Range

Set WsData = Worksheets("Data")
Set WsReport = Worksheets("Report")

With WsData
    ' look for the last used cell in column B
    Rl = .Cells(.Rows.Count, "A").End(xlUp).Row
    Set Rng = .Range(.Cells(Rl - 2, 1), .Cells(Rl, 1))
    ' Pastes the last 3 cells of Column A into the Month column
    Rng.Copy Destination:=WsReport.Cells(9, "B")
End With

With WsReport
    .Cells(8, "B").Formula = "Month"
    .Cells(8, "C").Formula = "Production Cost"
    .Cells(8, "D").Formula = "Month"
    .Cells(8, "E").Formula = "Inventory Cost"
    .Cells(8, "F").Formula = "Total cost"
    .Cells(12, "B").Formula = "Total"

    ' Calculates the Production cost
    '   there is no calculation unless there are formulas being copied
    Rng.Offset(0, 1).Copy Destination:=.Cells(9, "C")

    ' Calculates the Inventory cost
    '   there is no calculation
End With

当然，所有计算都将丢失，因为它们不包含在您的问题中.希望您能够将它们添加到我提供的代码中.

Of course, all the calculations are missing because they are not included in your question. I hope you will be able to add them to the code I have provided.

这篇关于将一张纸上的最后一行相乘并放置到另一张纸上的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！