excel 根据输入值更改单元格的值

wkyowqbh 于 2023-01-06 发布在其他

关注(0)|答案(1)|浏览(170)

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("d10:e309")) Is Nothing Then
        On Error GoTo bm_Safe_Exit
        Application.EnableEvents = False
        With Range("d10:e309")
            Select Case .Value2
                Case 0
                    'do nothing
                Case 1
                    .Value = 0.01
                Case 5
                    .Value = 0.005
            End Select
        End With
    End If

bm_Safe_Exit:
        Application.EnableEvents = True
End Sub

我希望代码在输入到单元格时使用1和5作为快捷方式，以便在分别输入为.01和.005的单元格中自动替换

excel

来源：https://stackoverflow.com/questions/74998377/change-value-of-cell-based-on-input-value

1条答案

按热度按时间

8tntrjer1#

Range("d10:e309").Value2将是一个数组。将数组与值进行比较是无效的。其次，如果多个单元格被更改（例如通过复制粘贴），Target.Value也可能是一个数组。它还可能包括您感兴趣范围之外的单元格

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim TargetRange As Range, rng As Range 
    Set TargetRange = Intersect(Target, Range("d10:e309"))
    If Not TargetRange Is Nothing Then
        On Error GoTo bm_Safe_Exit
        Application.EnableEvents = False
        For Each rng In TargetRange 
            Select Case rng.Value2
                Case 0
                    'do nothing
                Case 1
                    Rng.Value = 0.01
                Case 5
                    Rng.Value = 0.005
            End Select
        Next
    End If

bm_Safe_Exit:
    Application.EnableEvents = True
End Sub

赞(0）回复(0）举报 2023-01-06

我来回答

excel 根据输入值更改单元格的值

1条答案

相关问题

热门标签

最新问答