Excel-VBA 命名范围行总和

发布于 2024-10-03 13:50:25 字数 310 浏览 8 评论 0原文

我有以下代码

 For i = 1 To DepRng.Rows.Count
    For j = 1 To DepRng.Columns.Count
         DepRng.Cells(i, j) = Application.Sum(KidsRng.Row(i)) //Does not work
    Next j
 Next i

虽然我知道这是错误的，但我不知道如何将其存储在 DepRng.Cells(i, j) 中整个 KidsRng.Row[我] 有什么帮助吗？

原文

I have the following code

 For i = 1 To DepRng.Rows.Count
    For j = 1 To DepRng.Columns.Count
         DepRng.Cells(i, j) = Application.Sum(KidsRng.Row(i)) //Does not work
    Next j
 Next i

Although I know is wrong, i have no idea how to get it to store in DepRng.Cells(i, j) the total sum of the whole KidsRng.Row[i] Any help?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

[旋木] 2024-10-10 13:50:25

下面的代码工作正常。

也许您应该将其与您的进行比较：

Sub a()

Dim DepRng As Range
Dim kidsrng As Range
Set DepRng = Range("B1:B2")
Set kidsrng = Range("C1:F2")

 For i = 1 To DepRng.Rows.Count
      DepRng.Cells(i, 1) = Application.Sum(kidsrng.Rows(i))
 Next i

End Sub

只需用数字填充范围 C1:F2，执行宏后每行的总计将显示在 B1:B2 中。

The following code works ok.

Perhaps you should compare it with yours:

Sub a()

Dim DepRng As Range
Dim kidsrng As Range
Set DepRng = Range("B1:B2")
Set kidsrng = Range("C1:F2")

 For i = 1 To DepRng.Rows.Count
      DepRng.Cells(i, 1) = Application.Sum(kidsrng.Rows(i))
 Next i

End Sub

Just fill the range C1:F2 with numbers and the totals per row will appear in B1:B2 upon execution of the macro.

回复收藏 0 原文

祁梦 2024-10-10 13:50:25

已排序，感谢大家的帮助

   DepRng.Cells(i, j) = Application.Sum(KidsRng.Rows(i)) //just needed to add the "s" in rows

Sorted, thanks all for ur help

   DepRng.Cells(i, j) = Application.Sum(KidsRng.Rows(i)) //just needed to add the "s" in rows

回复收藏 0 原文

沫雨熙 2024-10-10 13:50:25

可能有比这更好的方法，但这是我的解决方案，它取决于内部 Excel 公式引擎，但它可能足以满足您正在做的事情...它确定 KidsRng.Row(i) 的完整地址，并将其输入 =SUM() 公式字符串并由 Application.Evaluate 进行评估。

For i = 1 To DepRng.Rows.Count
    For j = 1 To DepRng.Columns.Count

        DepRng.Cells(i, j).Value = Application.Evaluate("=SUM(" & KidsRng.Row(i).Address(True, True, xlA1, True) & ")")

    Next j
 Next i

如果 kidsrng 存在于不同的工作表/书中，则更新它以使其工作
更新为使用 Application.Evaluate

There may be a better way than this, but this is my solution which depends on the internal Excel formula engine though, it might be sufficient enough for what you're doing... It determines the full address of KidsRng.Row(i), and feeds it into a =SUM() formula string and evaluated by Application.Evaluate.

For i = 1 To DepRng.Rows.Count
    For j = 1 To DepRng.Columns.Count

        DepRng.Cells(i, j).Value = Application.Evaluate("=SUM(" & KidsRng.Row(i).Address(True, True, xlA1, True) & ")")

    Next j
 Next i

updated it to work if kidsrng existed in a different sheet/book
updated to use Application.Evaluate

回复收藏 0 原文

~没有更多了~