每当切换验证列表选项时，重新填充Excel数据以报告特定信息

Question

我正在开发一个计算制造标准成本的Excel财务模板，在一个选项卡上，我需要从列表(当前、增加/减少、NPI)中选择，告诉计算器使用“标准值”或“手动输入”，但来自同一个单元格。

文档保存后，一个新的WB将其设置为“当前”，并显示从“ROI- current”到“ROI-Target”选项卡中计算毛利率的当前“列表价格”、“批量价格”和“材料成本”。

但是，当您通过选项进行更改时，您需要将调用的值重写为任何需要的值。当你做过程改进的时候，你做了很多这样的事情，只是为了看看数字是如何移动的。因此，我正在寻找一种方法，以重新填充数据，一种特定的方式，每次一个列表选项被切换。

我想要完成的是，当“当前”被选中时，*列表价格$(75)，批量价格$(60)，材料成本$(18)，从ROI选项卡中被调用。(更可取的是，除非您更改了选项，比如在使用验证的错误消息时，否则任何东西都是不可修改的；但并不是必需的)。

然后，如果选择“增减”，则使用当前的$(但是可修改的字段)列出价格和材料成本。在此，批量价格计算为(CurTabBulk/CurTabList)xIncDecList，然后用于查找毛利率。

当选择“NPI”时，如果只修改清单价格，则将批量价格计算为清单价格的0.7倍，以假定材料成本以获得毛利率。作为所有这些的例子：

$75 List x 0.8 = $60 Wholesale X 0.3 = $18 Materials / $60 Wholesale * 100 = 70%毛利率(这是ROI的NPI市场预测--当前的Tab构建ROI - Target Tab.)

我可以直接将所有这些代码编码到单元格中，但是每当您输入自己的值时，该单元格中存在的代码就会被覆盖。因此，我正在寻找一种快速的脏方式，每次切换选项时都会以一种特定的方式重新填充它。

谢谢大家！

Answer 1

更新:所以我在今天的工作中使用了Alt-F11：

我找到了我放入VBA中的代码：

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$G$4" Then
Range("H4:I7").Value = "Select"
End If
End Sub

这将在选择类别后将所有验证框H4:I7设置为"Select“，但我希望每个单元格都使用该值进行更新，而不是继续选择它们，因为它是列表中的唯一值。

然后我找到了这个VBA，它会根据我的需要重置，但是我根本不知道如何使它工作.

Sub ResetDropDowns()
Dim rngLists As Range
Dim ListCell As Range
On Error Resume Next
Set rngLists = Sheets("Entry Sheet").UsedRange.SpecialCells(xlCellTypeAllValidation)

On Error GoTo 0
If Not rngLists Is Nothing Then
For Each ListCell In rngLists.Cells
ListCell.Value = Range(Trim(Mid(Replace(ListCell.Validation.Formula1, ":", String(99, " ")), 2, 99))).Value
Next ListCell
End If
End Sub

此外，在独立类别被选中后，我将需要在产品1和2下的白框中输入值。这些框根据选择的ROI类别移动: Current = No white；Adj/ Price =- List & Costs，NPI = List & GM。因此，我希望我可以重新分配代码，以便在更改时完成对正确单元格的“依赖的重新计算值”的调用。

请按照URL获取扩展页的图片。这是我在Product1List Price：=INDEX($E$43:$G$43，MATCH($G$4，$E$42:$G$42,0))的验证源(图像中的结果号)中输入的依赖列表代码。

谢谢大家的帮助。

https://www.excelforum.com/excel-programming-vba-macros/1387101-auto-populate-dependent-validation-list-with-fist-values-from-list.html

Answer 2

这解决了它。右击选项卡打开代码编辑器。复制并通过下面的窗口。将Sheet1和单元格更改为您需要的任何东西。使用插入的图标创建宏按钮，右键单击它并选择make宏。

Sub ResetDropDowns()

Dim rngLists As Range
Dim ListCell As Range

On Error Resume Next
Set rngLists = Sheets("Sheet1").Range("G21,H21")
On Error GoTo 0

If Not rngLists Is Nothing Then
For Each ListCell In rngLists.Cells
ListCell.Value = Range(Trim(Mid(Replace(ListCell.Validation.Formula1, ":", String(99, " ")), 2, 99))).Value
Next ListCell
End If

结束子对象