IfElse樹使用案例函式或VLOOKUP

我已經撰寫了這段代碼，目前可以運行，但是，我的 if/else 陳述句樹非常長且重復，只有少數更改是變數為每種情況設定的值。我已經閱讀了在Select Case測驗多個條件時使用 VBA 陳述句很好用的內容，對于我的案例測驗是什么以及wsName是什么rate_value。Select Case如果有人可以提供關于我如何使用該方法或使用 VLOOKUP 表的大綱，這就是我的代碼當前的樣子，我將不勝感激。供參考，wsName并rate_value在呼叫這些行之前定義。

If wsName = "Test-3" Then
   If rate_value < 50 Then
       snapdownvol = 95
   ElseIf rate_value = 50 Then
       snapdownvol = 98
       sweep_value = 49.8
       sweep_value_max = 50.2
   ElseIf rate_value = 100 Then
       snapdownvol = 110
       sweep_value = 99.8
       sweep_value_max = 100.2
   ElseIf rate_value = 200 Then
       snapdownvol = 110
       sweep_value = 199.4
       sweep_value_max = 200.4
   ElseIf rate_value > 200 Then
       MsgBox "Rate Value for " & sysnum & " is greater than 200 kHz. Rate Min and Max will be 0."
   End If
ElseIf wsName = "Test-6" Then
   If rate_value < 50 Then
       snapdownvol = 98
   ElseIf rate_value = 50 Then
       snapdownvol = 98
       sweep_value = 49.8
       sweep_value_max = 50.2
   ElseIf rate_value = 100 Then
       snapdownvol = 125
       sweep_value = 99.8
       sweep_value_max = 100.2
   ElseIf rate_value = 200 Then
       snapdownvol = 125
       sweep_value = 199.4
       sweep_value_max = 200.4
   ElseIf rate_value > 200 Then
       MsgBox "Rate Value for " & sysnum & " is greater than 200 kHz. Rate Min and Max will be 0."
   End If
ElseIf wsName = "Test-8" Then
   If rate_value < 50 Then
       snapdownvol = 98
   ElseIf rate_value = 50 Then
       snapdownvol = 98
       sweep_value = 49.8
       sweep_value_max = 50.2
   ElseIf rate_value = 100 Then
       snapdownvol = 125
       sweep_value = 99.8
       sweep_value_max = 100.2
   ElseIf rate_value = 200 Then
       snapdownvol = 125
       sweep_value = 199.4
       sweep_value_max = 200.4
   ElseIf rate_value > 200 Then
       MsgBox "Rate Value for " & sysnum & " is greater than 200 kHz. Rate Min and Max will be 0."
   End If
End If

uj5u.com熱心網友回復：

由于有很多重復，我會選擇案例選擇：

Select Case rate_value
    Case Is < 50
        Select Case wsName
            Case "Test-3"
                snapdownvol = 95
            Case "Test-6", "Test-8"
                snapdownvol = 98
        End Select
    Case Is = 50
        snapdownvol = 98
        sweep_value = 49.8
        sweep_value_max = 50.2
    Case Is = 100
        Select Case wsName
            Case "Test-3"
                snapdownvol = 110
            Case "Test-6", "Test-8"
                snapdownvol = 125
        End Select
        sweep_value = 99.8
        sweep_value_max = 100.2
    Case Is = 200
        snapdownvol = 110
        sweep_value = 199.4
        sweep_value_max = 200.4
    Case Is > 200
        MsgBox "Rate Value for " & sysnum & " is greater than 200 kHz. Rate Min and Max will be 0."
End Select

標籤：擅长vba

上一篇：在創建for回圈時需要幫助以將范圍內的每個非空單元格命名為唯一名稱

下一篇：有沒有辦法按名稱過濾VBA用戶表單的串列框專案？