当前位置：文江博客话题详情

循环遍历一系列单元格，将数据输入到其他单元格（下拉选择）并返回计算结果

发布于 2024-08-19 06:28:42 字数 1564 浏览 1 评论 0原文

我有一个用 Excel 2003 构建的计算器，您可以在其中从下拉菜单中选择选项，然后收集并计算数据，返回生产该项目包的总成本。我想要的是循环浏览此下拉列表的所有组合，并自动将总单位成本返回到另一个单元格。

我已经创建了一个巨大的网格，其中所有可能的下拉选择都输入到单独的工作表中。这是一次迭代的示例，但显然这需要循环，并且单元格引用需要随着移动而偏移。

标题为“成本计算器”的工作表中 E8 的单元格值需要 = 标题为“成本”的工作表中 G1 的单元格值标题
为“成本计算器”的工作表中 E9 的单元格值需要 = 工作表 A2 的单元格值标题为“成本计算器”
的工作表的标题为“成本”的 E10 单元格值需要 = 标题为“成本计算器”的工作表的 B2 的单元格值标题
为“成本计算器”的工作表的 E11 的单元格值需要 =标题为“成本”的工作表的 C2 标题
为“成本计算器”的工作表的 E12 的单元格值需要 = 标题为“成本”的工作表的 D2 的单元格值标题
为“成本计算器”的工作表的 E13 的单元格值需要 =标题为“成本”的工作表的 E2 单元格值
然后，需要复制标题为“成本计算器”的工作表的单元格 E22 中的计算结果，并将该值粘贴到标题为“成本”的工作表的 G2 中。

然后需要进行两次更改才能发生完全相同的事情。

标题为“成本计算器”的工作表的 E8 单元格值需要 = 标题为“成本”的工作表的 H1 单元格值（偏移一列），

标题为“成本计算器”的工作表的单元格 E22 中的计算结果需要为复制并将值粘贴到标题为“成本”的工作表的 H2 中，再次偏移一列。

这需要以相同的两个偏移量发生 21 次（包括初始时间）。

如果是这样的话，我会很高兴，我可以为每一行运行宏，但如果它也可以循环一行并继续运行，直到它到达一个空白单元格，那就太理想了。如果发生这种情况，则在经过 21 次之后，需要向下移动一行，如下所示：

标题为“成本计算器”的工作表中 E8 的单元格值需要 = 标题为“成本”的工作表中 G1 的单元格值“（始终位于第 1 行，因为这是数量）
标题为“成本计算器”的工作表中 E9 的单元格值需要 = 标题为“成本”的工作表中 A3 的单元格值标题为
“的工作表中 E10 的单元格值成本计算器”需要 = 标题为“成本”的工作表中 B3 的单元格值标题
为“成本计算器”的工作表中 E11 的单元格值需要 = 标题为“成本”的工作表中
E12 的单元格值标题为“成本计算器”的工作表需要 = 标题为“成本”的工作表中 D3 的单元格值标题
为“成本计算器”的工作表中 E13 的单元格值需要 = 标题为“成本”的工作表中 E3 的单元格值
然后需要复制标题为“成本计算器”的工作表的单元格 E22 中的计算结果，并将值粘贴到标题为“成本”的工作表的 G3 中。

然后与之前相同的偏移量 21 次并循环。

我什至不确定这是否可以用 Excel 实现，但我想如果有人能够提供帮助。如果您对我所问的内容有任何疑问，请告诉我，我已尽力进行描述。希望这个想法能够被传达，如果我需要重新格式化我的工作表的布局以适应也可以完成的代码。

原文

I have a calculator built in Excel 2003 in which you select options from a drop-down menu, data is then gathered and calculated returning your total cost to produce that package of items. What I'd like is to cycle through all the combinations of this drop-down and automatically return the total unit cost to another cell.

I have already created a massive grid with all possible drop-down selections entered in a separate worksheet. Here is an example of one iteration of this, but obviously this needs to loop and the cell references need to offset with the move.

Cell value for E8 of the worksheet entitled "Cost Calculator" needs to = the Cell value for G1 of the worksheet entitled "Costs"
Cell value for E9 of the worksheet entitled "Cost Calculator" needs to = the cell value for A2 of the worksheet entitled "Costs"
Cell value for E10 of the worksheet entitled "Cost Calculator" needs to = the cell value for B2 of the worksheet entitled "Costs"
Cell value for E11 of the worksheet entitled "Cost Calculator" needs to = the cell value for C2 of the worksheet entitled "Costs"
Cell value for E12 of the worksheet entitled "Cost Calculator" needs to = the cell value for D2 of the worksheet entitled "Costs"
Cell value for E13 of the worksheet entitled "Cost calculator" needs to = the cell value for E2 of the worksheet entitled "Costs"
Then the calculated result in cell E22 of the worksheet entitled "Cost Calculator" needs to be copied and the value pasted into G2 of the worksheet entitled "Costs".

Then the exact same thing needs to occur with 2 changes.

Cell value for E8 of the worksheet entitled "Cost Calculator" needs to = the Cell value for H1 of the worksheet entitled "Costs" (offset by one column)

and the calculated result in cell E22 of the worksheet entitled "Cost Calculator" needs to be copied and the value pasted into H2 of the worksheet entitled "Costs" again offset by one column.

This needs to occur 21 times (including the initial time) with the same two offsets.

I'd be happy if that were the case and I can just run the macro for each row, but if it could also cycle down a row and continue running until it hit a blank cell that would be ideal. If that were to occur, after the 21 times across it needs to move down a row and would be the following:

Cell value for E8 of the worksheet entitled "Cost Calculator" needs to = the Cell value for G1 of the worksheet entitled "Costs" (will always be in row 1 as this is the quantity)
Cell value for E9 of the worksheet entitled "Cost Calculator" needs to = the cell value for A3 of the worksheet entitled "Costs"
Cell value for E10 of the worksheet entitled "Cost Calculator" needs to = the cell value for B3 of the worksheet entitled "Costs"
Cell value for E11 of the worksheet entitled "Cost Calculator" needs to = the cell value for C3 of the worksheet entitled "Costs"
Cell value for E12 of the worksheet entitled "Cost Calculator" needs to = the cell value for D3 of the worksheet entitled "Costs"
Cell value for E13 of the worksheet entitled "Cost calculator" needs to = the cell value for E3 of the worksheet entitled "Costs"
Then the calculated result in cell E22 of the worksheet entitled "Cost Calculator" needs to be copied and the value pasted into G3 of the worksheet entitled "Costs".

Then the same offset as before 21 times and loop.

I'm not even sure if this is possible with Excel, but I figure if it is someone out here will be able to assist. Please let me know if you have any questions for what I am asking, I tried to be as descriptive as possible. Hopefully the idea is conveyed and if I need to reformat the layout of my sheets to accommodate the code that can be done as well.

分享到QQ

分享到微博

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

发布评论

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

陌上青苔 2024-08-26 06:28:42

我不确定这正是您正在寻找的，我被

然后与之前相同的偏移量21
次。并循环。

我创建了一些代码来执行以下操作

工作表 E8 的单元格值
标题为“成本计算器”需要=
工作表 G1 的单元格值
E9 的标题为“成本”的单元格值
题为“成本
计算器”需要 = 单元格值
工作表 A2 的标题为
E10 的“成本”单元格值
题为“成本计算器”的工作表
需要 = B2 的单元格值
标题为“成本”单元格的工作表
工作表 E11 的值
标题为“成本计算器”需要=
工作表 C2 的单元格值
E12 的标题为“成本”的单元格值
题为“成本
计算器”需要 = 单元格值
对于标题为 D2 的工作表
E13 的“成本”单元格值
题为“成本计算器”的工作表
需要 = E2 的单元格值
题为“成本”的工作表然后
单元格 E22 中的计算结果
题为“成本
计算器”需要复制并且
值粘贴到工作表的 G2 中
标题为“成本”。
然后需要完全相同的事情
发生 2 次更改。单元格值
题为“成本”的工作表 E8
计算器”需要 = 单元格值
工作表的 H1 标题为
“成本”（抵消一栏）和
计算结果位于单元格 E22 中
题为“成本计算器”的工作表
需要复制并且值
粘贴到工作表的 H2 中
题为“成本”的项目再次抵消一
专栏。

Sub Calc_Loop()
Dim lngRow As Long
Dim intOS As Integer
Dim intCol As Integer

  Sheets("Cost Calculator").Select
  lngRow = 2
  Do
    For intOS = 1 To 5
      'Set Cells E9 - E13 = to Cells A-E on row lngRow on Sheet Costs
      Cells(8 + intOS, 5) = Sheets("Costs").Cells(lngRow, intOS)
    Next intOS
    'Set E8 to G1 on Sheet Costs
    Cells(8, 5) = Sheets("Costs").Cells(1, 7)
    'Set G on row lngRow on Sheet Costs to E22
    Sheets("Costs").Cells(lngRow, 7) = Cells(22, 5)

    'Set E8 to H1 on Sheet Costs
    Cells(8, 5) = Sheets("Costs").Cells(1, 8)
    'Set H on row lngRow on Sheet Costs to E22
    Sheets("Costs").Cells(lngRow, 8) = Cells(22, 5)

    lngRow = lngRow + 1
  Loop Until IsEmpty(Sheets("Costs").Cells(lngRow, 1))
End Sub

因此，这将循环遍历工作表“成本”和：
* 插入 G2 作为 A2 - E2 与 G1 的计算输出
* 插入 H2 作为 A2 - E2 与 H1 的计算输出
* 插入 G3 以获取 A3 - E3 与 G1 的计算输出
* 插入 H3 作为 A3 - E3 和 H1 的计算输出...

这是您需要的吗？

I'm not certain this is exactly what you are looking for, I am being thrown by the

Then the same offset as before 21
times. And loop.

I created some code that will do the following

Cell value for E8 of the worksheet
entitled "Cost Calculator" needs to =
the Cell value for G1 of the worksheet
entitled "Costs" Cell value for E9 of
the worksheet entitled "Cost
Calculator" needs to = the cell value
for A2 of the worksheet entitled
"Costs" Cell value for E10 of the
worksheet entitled "Cost Calculator"
needs to = the cell value for B2 of
the worksheet entitled "Costs" Cell
value for E11 of the worksheet
entitled "Cost Calculator" needs to =
the cell value for C2 of the worksheet
entitled "Costs" Cell value for E12 of
the worksheet entitled "Cost
Calculator" needs to = the cell value
for D2 of the worksheet entitled
"Costs" Cell value for E13 of the
worksheet entitled "Cost calculator"
needs to = the cell value for E2 of
the worksheet entitled "Costs" Then
the calculated result in cell E22 of
the worksheet entitled "Cost
Calculator" needs to be copied and the
value pasted into G2 of the worksheet
entitled "Costs".
Then the exact same thing needs to
occur with 2 changes. Cell value for
E8 of the worksheet entitled "Cost
Calculator" needs to = the Cell value
for H1 of the worksheet entitled
"Costs" (offset by one column) and the
calculated result in cell E22 of the
worksheet entitled "Cost Calculator"
needs to be copied and the value
pasted into H2 of the worksheet
entitled "Costs" again offset by one
column.

Sub Calc_Loop()
Dim lngRow As Long
Dim intOS As Integer
Dim intCol As Integer

  Sheets("Cost Calculator").Select
  lngRow = 2
  Do
    For intOS = 1 To 5
      'Set Cells E9 - E13 = to Cells A-E on row lngRow on Sheet Costs
      Cells(8 + intOS, 5) = Sheets("Costs").Cells(lngRow, intOS)
    Next intOS
    'Set E8 to G1 on Sheet Costs
    Cells(8, 5) = Sheets("Costs").Cells(1, 7)
    'Set G on row lngRow on Sheet Costs to E22
    Sheets("Costs").Cells(lngRow, 7) = Cells(22, 5)

    'Set E8 to H1 on Sheet Costs
    Cells(8, 5) = Sheets("Costs").Cells(1, 8)
    'Set H on row lngRow on Sheet Costs to E22
    Sheets("Costs").Cells(lngRow, 8) = Cells(22, 5)

    lngRow = lngRow + 1
  Loop Until IsEmpty(Sheets("Costs").Cells(lngRow, 1))
End Sub

So this will loop through the Sheet "Costs" and:
* insert G2 for the calculated output from A2 - E2 with G1 and
* insert H2 for the calculated output from A2 - E2 with H1
* insert G3 for the calculated output from A3 - E3 with G1 and
* insert H3 for the calculated output from A3 - E3 with H1 ...

Is this what you need?

回复收藏 0 原文

~没有更多了~