Excel VBA 代码，根据相邻单元格中的选择，使用与单元格相邻的文本填充单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15285283/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA code to populate cell with text adjacent to cell based on choice in adjacent
提问by Grantos
Trying to write code to populate a cell adjacent to a cell with a drop-down menu, based on the choice made in the drop-down menu.
根据在下拉菜单中所做的选择，尝试编写代码以使用下拉菜单填充与单元格相邻的单元格。
I need this to apply to only the active sheet and not necessarily needed for the whole workbook.
我需要它仅适用于活动工作表，而不一定适用于整个工作簿。
Relatively new to writing code in VBA and probably a rookie mistake, but still need some help!
在 VBA 中编写代码相对较新，可能是新手错误，但仍然需要一些帮助！
Getting runt-time error Method 'Value' of object 'Range' failed.
获取运行时错误对象“范围”的方法“值”失败。
Here's the code I have, and it's the only code in the whole workbook...
这是我拥有的代码，它是整个工作簿中唯一的代码......
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Range("F18")
Case "Made Margin/Made Sales $"
Worksheets("Store #").Range("G18").value =
"Made margin by $XXX GIG at XX.XX%. Shrink at XX.XX% or $XXX. QTD Margin at
XX.XX%"
Case "Made Margin/Missed Sales $"
Worksheets("Store #").Range("G18").value =
"Made margin by $XXX GIG at XX.XX%. Shrink at XX.XX% or $XXX. QTD Margin at XX.XX%
Missed sales by $XXX. Begin explaining Sales $ miss here"
Case "Missed Margin/Made Sales$"
Worksheets("Store #").Range("G18").value = "Missed margin by $XXX GIG at XX.XX%.
Shrink at XX.XX% or $XXX. QTD Margin at XX.XX% Made sales by $XXX. Begin explaining
Margin $ miss here"
Case "Missed Margin/Missed Sales"
Worksheets("Store #").Range("G18").value = "Missed margin by $XXX GIG at XX.XX%.
Shrink at XX.XX% or $XXX. QTD Margin at XX.XX% Begin explaining Margin $ miss here,
followed by explaining Sales $ miss"
End Select
End Sub
Any help would be GREATLY appreciated!
任何帮助将不胜感激！
采纳答案by erstaples
You need to add a condition that only triggers the event when F18 is changed. That, and bring the string into one single line or use the "_" key to signal multi-lined code. I would also make sure there's only one set of quotes per case value or else that will mess up the code.
您需要添加一个条件，该条件仅在更改 F18 时触发事件。那，并将字符串变成一行或使用“_”键来表示多行代码。我还会确保每个 case 值只有一组引号，否则会弄乱代码。
I tried this and the code worked great.
我试过这个，代码工作得很好。
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("F18")) Is Nothing Then
Select Case Range("F18")
Case "Made Margin/Made Sales $"
Worksheets("Store #").Range("G18").Value = "Made margin by $XXX GIG at XX.XX%. Shrink at XX.XX% or $XXX. QTD Margin at"
Case "Made Margin/Missed Sales $"
Worksheets("Store #").Range("G18").Value = "Made margin by $XXX GIG at XX.XX%. Shrink at XX.XX% or $XXX. QTD Margin at XX.XX%. Missed sales by $XXX. Begin explaining Sales $ miss here"
Case "Missed Margin/Made Sales$"
Worksheets("Store #").Range("G18").Value = "Missed margin by $XXX GIG at XX.XX%. Shrink at XX.XX% or $XXX. QTD Margin at XX.XX% Made sales by $XXX. Begin explaining Margin $ miss here"
Case "Missed Margin/Missed Sales"
Worksheets("Store #").Range("G18").Value = "Missed margin by $XXX GIG at XX.XX%. Shrink at XX.XX% or $XXX. QTD Margin at XX.XX% Begin explaining Margin $ miss here, followed by explaining Sales $ miss"
End Select
End If
End Sub
