时间戳

首页 > 解决方案 > 选择正确的私人子

excel - 选择正确的私人子

问题描述

我有一些适用于控制按钮的 VBA 代码。代码工作正常，但我希望它自动运行。我似乎无法让 Private Sub 正确。代码如下：

Private Sub CommandButton3_Click()

Dim num As String

num = Range("L5").Value

Select Case num

        Case Is = 15, 16, 17, 18, 19

            Range("X5").Value = "510"

        Case Is = 20, 21, 22, 23, 24

            Range("X5").Value = "570"

        Case Is = 25, 26, 27, 28, 29

            Range("X5").Value = "610"

        Case Is = 30, 31, 32, 33, 34

            Range("X5").Value = "630"

        Case Is = 35, 36, 37, 38, 39

            Range("X5").Value = "635"

        Case Is = 40, 41, 42, 43, 44

            Range("X5").Value = "632"

        Case Is = 45, 46, 47, 48, 49

            Range("X5").Value = "622"

        Case Is = 50, 51, 52, 53, 54

            Range("X5").Value = "610"

        Case Is = 55, 56, 57, 58, 59

            Range("X5").Value = "590"

        Case Is = 60, 61, 62, 63, 64

            Range("X5").Value = "565"

        Case Is = 65, 66, 67, 68, 69

            Range("X5").Value = "540"

        Case Is = 70, 71, 72, 73, 74

            Range("X5").Value = "520"

        Case Is = 75, 76, 78, 79

            Range("X5").Value = "490"

        Case Is = 80, 81, 82, 83, 84

            Range("X5").Value = "470"

        Case Is = 85, 86, 87, 88, 89, 90

            Range("X5").Value = "440"

 End Select

 End Sub

标签： excelvba

解决方案

您不需要 case 语句，您可以在 1 行代码中完成（加上 if 以捕获范围之外的任何内容），因为您的括号以统一的方式递增：

Public Function MyFunction2(ByVal MyValue As Double) As Double
If MyValue < 15 Or MyValue > 90 Then
    MyFunction2 = 0
Else
    MyFunction2 = CDbl(Array(0, 0, 0, 510, 570, 610, 630, 635, 632, 622, 610, 590, 565, 540, 520, 490, 470, 440)(Round(MyValue / 5, 0)))
End Function

这是一个 UDF，因此遵循与 ja72 的答案相同的逻辑，但将 MyFunction2 而不是 MyFunction 并排放置，以便您可以比较它们都给出相同的结果。

推荐阅读