英文:
VBA How can I add a formula that multiplies two values in a Dynamic Table?
问题
我没有很多提问这些问题的经验，请谅解。我有以下情况。我有一大批数据，我正试图自动化，并需要执行一些计算。在列E上，我已经编写了代码，该代码会遍历整个报告，并在找到的每个空单元格上添加一个公式，该公式将销售总额（列F中的小计）除以利息金额（列G中的总额）。
现在，在列E中有了这个新值，我需要对列G中的每个条目运行独立的乘法运算。我需要将列E中的小计金额与列F中的每个个体金额相乘，以获取每行所欠利息的相应利息。
我遇到的主要问题是，我无法找到一种正确填充整个列G的公式的方法，因为每个子集的长度不断变化（动态变化）。
表格的外观如下，我有20多本书，但只是简化了一下：
任何想法或帮助都将不胜感激。
这是我到目前为止编写的代码：
Dim Rng As Range, r As Range
Set Rng = Range("E2:E" & Range("E" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeConstants)
For Each r In Rng.Areas
With r
.Cells(1, 1).Offset(.Rows.Count).Formula = "=OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())), 0, 2) / OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())), 0, 1)"
End With
Next
英文:
I don't have much experience asking these questions so please bare with me. I have the following scenario. I have a big range of data that I'm trying to automate and need to perform some calculations. On column E, I've written code that runs through the entire report and adds a formula on each empty cell found, which divides the total amount of sales (subtotal in column F) by the interest amount (Total in Column G).
Now that I have this new value in column E, I need to run an independent multiplication for each entry in Column G. I need to multiply the subtotal amount I have in Column E by each individual amount in Column F in order to get the corresponding interest for owed interest for each row.
The main problem I'm encountering is that I can't figure a way to fill in the entire Column G with the correct formula since the length of each subset is constantly changing (dynamic).
This is how the table looks like, I have over 20 books but just simplifying it:
Any ideas or help will be much appreciated.
This is the code I've written so far:
Dim Rng As Range, r As Range
Set Rng = Range("E2:E" & Range("E" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeConstants)
'Top = Selection.End(xlUp).Value
For Each r In Rng.Areas
With r
.Cells(1, 1).Offset(.Rows.Count).Formula = "=OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())), 0, 2) / OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())), 0, 1)"
End With
Next
答案1
得分: 2
尝试类似这样的代码：
Sub Tester()
Dim Rng As Range, rE As Range, rG As Range
Set Rng = Range("E2:E" & Range("E" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeConstants)
For Each rE In Rng.Areas
Set rG = rE.EntireRow.Columns("G") '对应的 G 范围
rG.Formula = "=" & rE.Cells(1).Offset(0, 1).Address(False, False) & _
"*" & rE.Cells(rE.Cells.Count).Offset(1).Address(True, True)
Next
End Sub
英文:
Try something like this -
Sub Tester()
Dim Rng As Range, rE As Range, rG As Range
Set Rng = Range("E2:E" & Range("E" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeConstants)
For Each rE In Rng.Areas
Set rG = rE.EntireRow.Columns("G") 'corresponding G range
rG.Formula = "=" & rE.Cells(1).Offset(0, 1).Address(False, False) & _
"*" & rE.Cells(rE.Cells.Count).Offset(1).Address(True, True)
Next
End Sub
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论