为了提出这个问题，我简化了我的问题。 想象一下，我在Excel中有以下数据：
现金流量| DiscountFactor
11,851 | 0.9901
96,401 | 0.9679
80,412 | 0.9494
我想在VBA中对这两列执行SUMPRODUCT，但不使用内置的SUMPRODUCT函数。 这是因为我的项目将变得更加复杂 - 但我只需要开始考虑这一点。 我的代码到目前为止：
Sub DiscountedCashflows() Dim CashFlows(1 To 3) As Variant Dim DiscountFactors(1 To 3) As Variant Dim CashFlowsElms 'Elements in the cashflow array Dim DiscountFactorElms 'Elements in the DiscountFactors array Sheet1.Select 'Populating Cashflows array Dim Counter1 As Long For Counter1 = LBound(CashFlows) To UBound(CashFlows) CashFlows(Counter1) = Range("A1").Offset(Counter1, 0).Value Next Counter1 'Populating DiscountFactors array Dim Counter2 As Long For Counter2 = LBound(DiscountFactors) To UBound(DiscountFactors) DiscountFactors(Counter2) = Range("B1").Offset(Counter2, 0).Value Next Counter2 'Loop through the elements in the first array For Each CashFlowsElms In CashFlows 'Loop through the elements in the second array For Each DiscountFactorElms In DiscountFactors x = x + 1 'Multiply the two array elements together Cells(x, 1) = CashFlowElms * DiscountFactorElms Next DiscountFactorElms Next CashFlowsElms MsgBox "Answer is..." Erase CashFlows Erase DiscountFactors End SubCashflows | DiscountFactor
11,851 | 0.9901
96,401 | 0.9679
80,412 | 0.9494
I want to perform a SUMPRODUCT on these two columns in VBA but without using the built in SUMPRODUCT Function. This is because my project is going to get more complicated than that - but I just need to figure this out to begin with. My code so far:
Sub DiscountedCashflows() Dim CashFlows(1 To 3) As Variant Dim DiscountFactors(1 To 3) As Variant Dim CashFlowsElms 'Elements in the cashflow array Dim DiscountFactorElms 'Elements in the DiscountFactors array Sheet1.Select 'Populating Cashflows array Dim Counter1 As Long For Counter1 = LBound(CashFlows) To UBound(CashFlows) CashFlows(Counter1) = Range("A1").Offset(Counter1, 0).Value Next Counter1 'Populating DiscountFactors array Dim Counter2 As Long For Counter2 = LBound(DiscountFactors) To UBound(DiscountFactors) DiscountFactors(Counter2) = Range("B1").Offset(Counter2, 0).Value Next Counter2 'Loop through the elements in the first array For Each CashFlowsElms In CashFlows 'Loop through the elements in the second array For Each DiscountFactorElms In DiscountFactors x = x + 1 'Multiply the two array elements together Cells(x, 1) = CashFlowElms * DiscountFactorElms Next DiscountFactorElms Next CashFlowsElms MsgBox "Answer is..." Erase CashFlows Erase DiscountFactors End SubHow do I get the code to output the correct answer?
To give some context I'll be expanding this to work for dynamic arrays and I'll eventually turn it into a user defined function.
Any help is appreciated.
最满意答案
主要问题是你只想循环一次并引用同一行。 为此，您将使用一个简单的for循环并使用计数器作为两个数组的索引。
没有理由使用循环加载数组，因为您可以直接分配值。
Sub DiscountedCashflows() Dim CashFlows() As Variant Dim DiscountFactors() As Variant Dim i As Long Dim temp As Double With Worksheets("Sheet16") 'Populating Cashflows array CashFlows = .Range("A2:A4").Value 'Populating DiscountFactors array DiscountFactors = .Range("B2:B4").Value 'Loop through the elements in the first array For i = LBound(CashFlows, 1) To UBound(CashFlows, 1) temp = temp + (CashFlows(i, 1) * DiscountFactors(i, 1)) Next i End With MsgBox "Answer is..." & temp End SubThe main problem is you only want to loop once and refer to the same row. To do that you would use a simple for loop and use the counter as the index of both arrays.
There is no reason to load the arrays with a loop, as you can assign the values directly.
Sub DiscountedCashflows() Dim CashFlows() As Variant Dim DiscountFactors() As Variant Dim i As Long Dim temp As Double With Worksheets("Sheet16") 'Populating Cashflows array CashFlows = .Range("A2:A4").Value 'Populating DiscountFactors array DiscountFactors = .Range("B2:B4").Value 'Loop through the elements in the first array For i = LBound(CashFlows, 1) To UBound(CashFlows, 1) temp = temp + (CashFlows(i, 1) * DiscountFactors(i, 1)) Next i End With MsgBox "Answer is..." & temp End Sub更多推荐
发布评论