根据输入的值更改单元格的值 [英] Change the value of cell based on the value inputted

问题描述

我正在尝试根据用户输入单元格的内容来创建公式语句.如果用户输入0，1或2，则相同"单元格的值需要更改.例如:

I'm trying to create a formula statement based on what the user enters into the cell. If a user enters a 0, 1 or 2, the value of THE SAME cell needs to change. For example:

如果用户在单元格E2中输入"1"，则需要更改为"2500"
如果用户在单元格E2中输入"0"，则需要保持为0
如果用户在单元格E2中输入"2"，则需要更改为"5000"

if a user enters "1" into cell E2, it needs to change to "2500"
if a user enters "0" into cell E2, it needs to stay at 0
if a user enters "2" into cell E2, it needs to change to "5000"

这有可能实现吗?
如果是这样，什么公式是必要的? 我尝试过其他方法，但没有走运，因为我不断遇到循环错误.这是否意味着不可能?
可能是VBA吗?

Is this possible to achieve?
If so, what formula is necessary? I've tried different ways without luck, because I keep getting a circular loop error. Does that mean this is not possible?
Possibly with VBA?

推荐答案

您将不得不使用某些VBA来解决此问题；特别是 Worksheet_Change 事件宏.如果您用值过度键入公式，则公式将无法重写它们所在的单元格.

You will have to attack this problem with some VBA; specifically a Worksheet_Change event macro. Formulas cannot rewrite the cell that they are in if you over-type them with a value.

右键单击工作表的名称标签，然后选择查看代码.当VBE打开时，将以下内容粘贴到工作表代码窗格中，标题为 Book1-Sheet1(代码).

Right-click the worksheet's name tab and choose View Code. When the VBE opens, paste the following into the worksheet code pane titled something like Book1 - Sheet1 (Code).

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("E2")) Is Nothing Then
        On Error GoTo bm_Safe_Exit
        Application.EnableEvents = False
        With Range("E2")
            Select Case .Value2
                Case 0
                    'do nothing
                Case 1
                    .Value = 2500
                Case 2
                    .Value = 5000
                Case Else
                    'possibly clear the cell contents as undesirable
                    '.ClearContents
            End Select
        End With
    End If

bm_Safe_Exit:
        Application.EnableEvents = True
End Sub

我在代码行中留下了注释，以删除不是 0、1 或 2 的所有条目.如果这是首选行为，请取消注释.

I've left a commented code line to remove any entry that is not a 0, 1 or 2. Uncomment it if that is the preferred behavior.

点击 Alt + Q 返回工作表.将这三个值中的任何一个键入到E2中都应该具有预期的结果.

Tap Alt+Q to return to the worksheet. Typing any of the three values into E2 should have the desired results.

请注意， Application.EnableEvents属性已暂时关闭.这是因为您正在更改工作表上的值，并且希望避免启动另一个事件，该事件将试图使例程在自身之上运行.

Note that Application.EnableEvents property is temporarily turned off. This is because you are changing a value on the worksheet and want to avoid initiating another event that will attempt to have the routine walk on top of itself.

这篇关于根据输入的值更改单元格的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！