vba 查找列中的最后一个空单元格，然后对上面的所有单元格求和
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20442974/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Find Last Empty Cell in a Column & then Sum all the Cells Above
提问by Malik
I am new to VB in Excel and need help.
我是 Excel 中的 VB 新手，需要帮助。
I have a spread sheet of Nominal Journal. Column B consists of Nominal Codes. Column J consists of debits whereas, Column K consists of Credits.
我有一份 Nominal Journal 的电子表格。B 栏由名义代码组成。J 列由借方组成，而 K 列由贷方组成。
Every week depending on the number of transactions, the length of rows occupied by data will be different i.e., in one week, we could have data from row # 01 to row # 100, in another week we could have data from row 01 to row 180 or row 85.
每周根据事务的数量，数据占用的行长会有所不同，即在一周内，我们可以从第 01 行到第 100 行的数据，再过一周我们可以从第 01 行到第 100 行的数据180 或第 85 行。
The first row is the header row followed by the data.
第一行是标题行，后面是数据。
My Questions:
我的问题：
I want to find the last row in column J and K and move the cursor in the next empty cell. For example if the last row is row # 100, then I want the cursor to move into J101. I have achieved this.
I want to do a sum total of column J in cell no J101 of all those rows whose nominal code begins with "7".
Likewise, I want a sum total of column K in cell K101 of all those rows whose nominal code begins with "7".
我想在 J 和 K 列中找到最后一行并将光标移动到下一个空单元格中。例如，如果最后一行是第 100 行，那么我希望光标移动到 J101。我已经做到了这一点。
我想对标称代码以“7”开头的所有行的单元格编号 J101 中的列 J 求和。
同样，我想要所有那些标称代码以“7”开头的行的单元格 K101 中列 K 的总和。
I have written the following code but the problem is when I run the macro, the sumproduct function takes into account the cell in which the total is going to be placed, i.e., the sum range is from B2:B101 instead of B2:B100 and J2:J101 instead of J2:J100.
我编写了以下代码，但问题是当我运行宏时， sumproduct 函数会考虑将要放置总数的单元格，即总和范围是从 B2:B101 而不是 B2:B100 和J2:J101 而不是 J2:J100。
Following is the copy of the code:
以下是代码的副本：
Sub Macro6()
'
' Macro6 Macro
' Column Totals
'
' Keyboard Shortcut: Ctrl+Shift+L
'
Range("J1").Select
ActiveCell.End(xlDown).Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=SUMPRODUCT((LEFT(R2C2:RC2,1)+0=7)*(R2C:RC))"
Range("J143").Select
End Sub
Any Help will be much appreciated.
任何帮助都感激不尽。
采纳答案by Siddharth Rout
Is this what you are trying? I have commented the code, so you shouldn't have problem understanding the code. But if you still do then post back.
这是你正在尝试的吗？我已经注释了代码，所以你理解代码应该没有问题。但是，如果您仍然这样做，请回帖。
Option Explicit
Sub Sample()
Dim ws As Worksheet
Dim lRow As Long, frmlaRow As Long
'~~> Set this to the relevant worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")
With ws
'~~> Find the last row in Col J
lRow = .Range("J" & .Rows.Count).End(xlUp).Row
'~~> row where the formula will be inserted
frmlaRow = lRow + 1
.Range("J" & frmlaRow).Formula = "=SUMPRODUCT((LEFT($B:$B$" & lRow & _
",1)=7)*($J:$J$" & lRow & "))"
'~~> Find the last row in Col K
lRow = .Range("K" & .Rows.Count).End(xlUp).Row
'~~> row where the formula will be inserted
frmlaRow = lRow + 1
.Range("K" & frmlaRow).Formula = "=SUMPRODUCT((LEFT($B:$B$" & lRow & _
",1)=7)*($K:$K$" & lRow & "))"
End With
End Sub
