使VBA Excel功能与参考单元一起动态 [英] Make vba excel function dynamic with the reference cells

问题描述

我编写了一个代码，该代码创建两个范围的总和....但是，当计算其总和的参考范围更改时，我希望最终答案改变

I have written a code which creates a sumproduct of two range.... But I want the final answer to change when the reference ranges whose sumproduct is been calculated is changed

p = 1
For p = 1 To 12
ActiveSheet.Cells(LastRow + 2, 31 + p).Formula = WorksheetFunction.SumProduct(Range(Cells(3, 31 + p), Cells(LastRow, 31 + p)), Range(Cells(3, 6 + p), Cells(LastRow, 6 + p)))
Next p

例如，在上一页中，最后乘积+第2行以及从第31列到第42列显示了求和结果，但是当我更改参考单元格时，答案不变 如何通过输入使最终结果动态化

for example in the above sheet the sumproduct result is shown in the lastrow+2 th row and from column 31 to 42, but when i change the reference cells the answer doesnt change how to make my end result dynamic with the input

推荐答案

对于.Formula，您需要添加一个公式，就像您将其手动添加到.Formula("=SumProduct(…)")这样的Excel单元格一样，这样Excel会自动重新计算该公式.要获取范围的地址并将其用于公式中，请使用范围.地址方法.

With .Formula you need to add a formula like you would add it manually into the Excel cell like .Formula("=SumProduct(…)") so the formula is recalculated by Excel automatically. To get the address of a range and use it in your formula use the Range.Address method.

代替循环，只需填写第一个单元格p = 1

Instead of a loop just fill out the formula for the first cell p = 1

ActiveSheet.Cells(LastRow + 2, 32).Formula = "=SumProduct(" & Range(Cells(3, 32), Cells(LastRow, 32)).Address(False, False) & ", " & Range(Cells(3, 7), Cells(LastRow, 7)).Address(False, False) & ")"

，然后将该单元格自动填充到其他单元格中(直到p = 12)

and then auto fill that cell right into the others (up to p = 12)

ActiveSheet.Cells(LastRow + 2, 32).AutoFill Destination:=Range(Cells(LastRow + 2, 32), Cells(LastRow + 2, 43))

此后，如果您更改任何值，求和乘积将自动重新计算.

After that the sum product automatically recalculates if you change any values.

这篇关于使VBA Excel功能与参考单元一起动态的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！